Solved

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

Posted on 2004-03-31
5
1,952 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 51

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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