asked on

docmd.runsql vs db.execute - Why is the latter faster?

Hi Folks,

Everything I've read points to the above statement, but what exactly is running faster? Is it the parsing of the SQL statement, move efficient moving of data?

I'm just interested if anyone knows.

Later,

Jes

Steve Bink

Here's one:
https://www.experts-exchange.com/questions/20629573/What-is-wrong-with-DoCmd-RunSQL.html

Here's another:
http://dbforums.com/t638614.html

Not too much information on the why's and wherefore's of the difference, but everyone agrees that .Execute is faster. My guess is that DoCmd sets up an additional connection or workspace or something behind the scenes, transparent to the user. CurrentDB.Execute has a built-in always on connection to the currently open database. It could also be that one talks to Jet through Access while the other talks directly to Jet. If anyone else can lend more info, I'd be interested in hearing the answer as well.

ASKER CERTIFIED SOLUTION

flavo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phileoca

they are both correct.

However, i dont mind the very MINIMAL slowdown when i can use the DoCmd and have a very clean easy to read code and easy to find errors.

locquery = "blah blah blah"
locQuery = locQuery & "la la la"
locQuery = locQuery & "ho ho ho"

docmd.runsql locQuery

but i'd give the points to one of those 2. =)

Data-Man

one other comment on using the currentdb.execute You don't have to turn off/on the setwarnings. You can also add the dbFailOnError constant to the end...error trapping!!!! woohooo

strSQL = "Blah blah ho ho he he la la"
CurrentDB.Execute strSQL, dbFailOnError

Mike

SoggyP

ASKER

Hi Folks,

Thanks for all the responses, I hope it's helped others.

Later,

Jes