Advice about DoCmd.OpenQuery method

[MarvinM]

Hi Experts,

I don't have a problem, but am just seeking advice.

Usually in my VBA procedures, when I need to run a query, I build an SQL string and then do a

   db.Execute (strSQL)

This allows me to put in variables into my query string. You all know that. This usually involves declaring the variables, opening the db, assembling a few lines of string text, executing the SQL, closing the db. Typically about 9-15 lines of code.

Now if there is no need to plug in any variable values, I could set up an Access query and run the DoCmd.OpenQuery method in one line. I am tempted to do this in a few places, but I wanted to know if there are any pros & cons. I can see the reduction in LOC's, but is there any reason why I should not do this?

Thank you for sharing your wisdom,
Marvin M

 

[jdraw]

You can only do
db.execute on an action query (insert, update, delete) and I recommend using the parameter dbFailOnError. This will notify you of an error, if one should occur.

 

[MarvinM]

Right. I should have been more clear. I am talking about action queries. What I do most often is pull a subset out of the back end database into a temp table for the user to work with locally. The users also send their adds and updates to the back end database.

An example of my dilemma is in this VBA code for empty and append (or DELETE and INSERT, if you will) queries for loading one of those temp tables. I currently do this:

   Dim strSQL As String
   Dim db As Database
 
   Set db = CurrentDb
   db.Execute "DELETE tmpPeople.* FROM tmpPeople;"
   strSQL = "INSERT INTO tmpPeople ( NUID, F_Name, M_Name, L_Name, Role, Archive )" & _
    " SELECT tblPeople.NUID, tblPeople.F_Name, tblPeople.M_Name," & _
    " tblPeople.L_Name, tblPeople.Role, tblPeople.Archive" & _
    " FROM tblPeople" & _
    " WHERE tblPeople.Archive=False;"
 
   db.Execute (strSQL)
   db.Close

But, in cases like this where the query criteria never changes, maybe I should be doing this:

   DoCmd.OpenQuery "qryEmpty_tmpPeople"
   DoCmd.OpenQuery "qryAppend_tmpPeople"

Does anyone know of any performance differences between these two approaches? Any opinions?

Thanks,
Marvin M

 

[jdraw]

Seems 6 of 1 vs half a dozen??
You have to be in the database to OpenQuery or
execute the sql.
Haven't tested but you could probably do something along this line (unsure of syntax)

qrydef.execute

but simplest from code is probably the DoCmd.OpenQuery

 

[gemma-the-husky]

there ARE differences

currentdb.execute query will fail on error

docmd.openquery will warn/advise you of progress, and will succeed with errors. if you suppress warnings the query will just run, and you will not be aware of any errors

both have their uses, so it depends exactly what you are doing.

 

[spikepl]

One factor to consider [err, make that two factors!] is frequency of application and duration of execution. An on-the-fly query has a slight overhead of creating an execution plan, but that plan fits the data. A stored query has a stored execution plan that fits the dataset as it was at the time of creation, unless that query was subsequently reopened and saved (AFAIK).

So which is best? That depends. If your query runs once in a blue moon then it makes no difference. If it runs thousands of thousands of times each day then experiment and measure.

 

[MarvinM]

Thanks, Spike & Dave.

With the current project, I'd estimate that these queries will run daily, maybe dozens, possibly over a hundred times per day. The database isn't loaded full of data yet, only 100 test cases. Testing won't tell me much right now. I'm sure the system will run a little differently when 10,000 records go in there.

I was hoping for some insight, like Spike's mention about the execution plan. That's making me favor the DoCmd.OpenQuery method. If there are more opinions, I'd love to hear them.

Thank you,
Marvin M