Solved

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

Posted on 2004-03-31
5
1,795 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now