Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 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