By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,509 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

Runnning an Action query from code

P: 2
For many years if I wanted to run an action query from code, I've used code something like this ---
Expand|Select|Wrap|Line Numbers
  1. dim qd as query
  2. dim sSQL as string
  3. dim db as database
  4.  
  5. set db = currentdb()
  6. sSQL = "UPDATE tableA SET field1 = 999 WHERE field2 > 0"
  7. set qd = db.createquerydef("",sSQL)
  8. qd.execute ' sometimes with dbFailOnError
  9.  
  10. qd.close
  11. set qd=nothing
  12.  
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
Aug 12 '07 #1
Share this Question
Share on Google+
1 Reply


JKing
Expert 100+
P: 1,206
Hi there. Have a look at this article it might be what you're looking for: Stored Query vs. SQL Statement

Jared
Aug 13 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.