[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2286
  • Last Modified:

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
0
SoggyP
Asked:
SoggyP
1 Solution
 
Steve BinkCommented:
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
 
flavoCommented:
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
 
phileocaCommented:
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
 
Data-ManCommented:
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
 
SoggyPAuthor Commented:
Hi Folks,

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

Later,

Jes
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now