Which is faster ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I'd like to know which one of these functions is faster:

dim strsql as string
strsql = "SELECT * FROM ... INTO ..."
docmd.runsql strsql

OR

dim strsql as string
strsql = "SELECT * FROM ... INTO ..."
currentdb.execute strsql

My opinion is that currentdb.execute should be faster because it's not using
a macro. But I may be wrong. If you have an idea please post !!!

Thanks in advance

Francois Houde
 
The Execute method is faster. DoCmd is an Access function, not a VB
function (there is a difference) and so the VB code has to pass it back to
Access to evaluate. I once created database to test it (I still have it
somewhere), that evaluted both of these in addition to creating and
executing a querydef. Execute is fastest.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Francois,

I use two variables, tBefore and tAfter (both of Date type). Before the
code begins, I capture the time once and after all the code is done, I
capture the time into tAfter and the do a simple subtraction.

I just did mine using the same method and pretty enlightening.

HTH,

Ben
 
FWIW, I just did a simple test, running 1000 iterations of a make-table.

DoCmd.RunSQL 1:54
DoCmd.OpenQuery 1:40
CurrentDb.Execute 0:33
 
Apart from which one is faster, I'm begining to migrate from
DoCmd.RunSQL to the .Execute method since you can do all sorts of fun
things like trapping errors, providing custom warning messages as to
number of records to be effected, canceling the operation via a
transaction, and whatnot.
 
I was referring to figuring out how to handle a particular error when it
occurrs at a critical point where you need to decide how to recover from
it and deal with the problem. (As opposed to a message box that reads
'MS Access has crapped out. Have a nice day.')
 
Back
Top