Solved

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

Posted on 2004-03-31
5
1,872 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:SoggyP
5 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10726682
Here's one:
http://www.experts-exchange.com/Databases/MS_Access/Q_20629573.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.
0
 
LVL 34

Accepted Solution

by:
flavo earned 25 total points
ID: 10727273
from MS vba optimisation doc { http://www.microsoft.com/officedev/articles/movs101.htm }

When running an action query from your application, you have
three choices: you can use the RunSQL macro action, the Execute
method of a database object to run SQL code, or you can create a
QueryDef object and then use its Execute method. Using the
Execute method of the database object or creating a temporary
querydef and using its Execute method take about the same
amount of time. On the other hand, using the Execute method of a
database object requires one less line of code and seems like a
simpler solution. Either solution is significantly faster than using
DoCmd RunSQL.
The sample code shows two ways to accomplish the same goal:
deleting all the rows from tblContacts. The slower method uses the
RunSQL action to run the SQL string:
DELETE * From tblContacts
The faster method uses the Execute method of the current
database to execute the SQL string.
Top
Using Temporary QueryDefs
Access provides a useful mechanism for creating temporary
QueryDef objects: just don't provide a name! If you use a zerolength
string for the name parameter of the CreateQueryDef()
method, Access creates a temporary "in-memory" query. You no
longer have to worry about the proliferation of querydefs and name
collisions, and since Access doesn't write temporary querydefs to
disk, they're a lot faster, too. Another plus: you don't have to
delete temporary querydefs- -they automatically disappear when
the QueryDef object goes out of scope. For example, you can use
code like this to create a temporary querydef:
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", _
"SELECT * FROM tblCustomers WHERE Age > 30;")
0
 
LVL 11

Expert Comment

by:phileoca
ID: 10728112
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. =)
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10728662
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
0
 

Author Comment

by:SoggyP
ID: 10730153
Hi Folks,

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

Later,

Jes
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question