Runnning an Action query from code

[ezderek]

For many years if I wanted to run an action query from code, I've used code something like this ---

dim qd as query
dim sSQL as string
dim db as database
 
set db = currentdb()
sSQL = "UPDATE tableA SET field1 = 999 WHERE field2 > 0"
set qd = db.createquerydef("",sSQL)
qd.execute ' sometimes with dbFailOnError
 
qd.close
set qd=nothing
 

etc.....

This creates and runs a temporary query (using DAO. Please don't reply and tell me I should use ADO -- I have my reasons for using DAO)

However over the years as the application has grown and the number of action queries has grown, this method has started to cause trouble. The problem is of course that Access does not reclaim the temporary working space used to create and run the queries. So the database bloats and bloats in normal use. If a user runs the compact function everything is OK again, for a while. But users simply can't be "trained" to do this reliably, and I'd rather sell a product that doesn't bloat itself in the first place, with it's inherent risk of corruption and crashing. (Yes of course my data is split from the app.)

So lately I've been coding differently -- I save all action queries as precompiled queries, and now run them with code something like this --

docmd.openquery "MyActionQuery"

The code is a lot simpler of course. The database is a little bigger initially because of all the pre-compiled queries, but it doesn't seem to bloat as much.
My question is this: What kind of an efficiency hit am I taking by using the "docmd" object? It seems like an unwieldy object with a lot of methods, and I'm not sure if it is the fastest and most efficient way to get the job done for a query.... what do you think? Additionally, as far as I know, no kind of "cleanup" is necessary afterwards, eg no "close" statements or anything like that. Am I right about this?

Derek

[JKing]

Hi there. Have a look at this article it might be what you're looking for: Stored Query vs. SQL Statement

Jared