RunSQL vs Execute

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any difference in efficiency between:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempADHS"
DoCmd.SetWarnings True

and

currentproject.connection.execute "DELETE * FROM tempADHS"
 
Not sure about the efficiency but I always use .Execute, for the following
reasons:

1. Less typing (I'm a programmer, and therefore lazy :-))
2. No need to turn warnings off, or remember to turn them back on.
3. You can execute the queries as a transaction giving you rollback
abilities.

The only time I deviate from this is in the (rare) occurrence that .Execute
will not work (for some reason it won't with some queries).

I'd guess that any efficiency differences (if any) will be minimal.

Ed Metcalfe.
 
I would also point out that Execute allows using other advanced query
settings such as the type of update, passthru and odbc specific
options.
 
Efficiency, eh?

In some simple queries, the Execute method will be faster, because it
doesn't call the Expression Service (to evaluate parameters.)

When you use the Execute method in code, you know that the query has run to
completion before the next line of code executes. You can't be sure of that
with RunSQL, so there might be cases where the code resumes before the
RunSQL completes. If so, would you consider the RunSQL to be faster
(releasing earlier) or less reliable (you may be trying to perform further
operations on the table that hasn't completed yet?) I can't be sure of that
scenario with RunSQL, whereas I know for sure I can trust the Exeucte method
(e.g. testing RecordsAffected on the next line of code.)

Summary of other differences:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html
 
currentproject.connection.execute "DELETE * FROM tempADHS"
Click to expand...

Can someone explain to me why one would use ADO instead of:

CurrentDB.Execute "DELETE * FROM tempADHS"

Why go through a separate layer to get to Jet when all you're doing
is handing off a SQL command to Jet?
 
RunSQL will solve the references FORMS!formName!ControlName for you, while
execute will fail, if any is present, complaining about missing values for
parameters. Sure, you can built the string so that such reference is outside
the string, and concatenated to it, but then, you have to be very careful
about all the possible case and required delimiters. RunSQL is thus much
easier (and independent of the Regional Settings), if you use
FORMS!formName!ControlName syntax.

Vanderghast, Access MVP
 
Some Jet 4.0 extensions, under default settings, require ADO, instead of
DAO, to work properly. No case where the reverse is observable come to mind.


Vanderghast, Access MVP.
 
Vanderghast, Access MVP.



Some Jet 4.0 extensions, under default settings, require ADO,
instead of DAO, to work properly. No case where the reverse is
observable come to mind.
Click to expand...

But those Jet 4 features are not accessible through *SQL*, right?

So, they don't actually fall within the scope of my question.
 
Yes, these are JET 4 SQL extensions, but if your SQL statement using them is
called with a DAO.Database, it will fail, with an error, the statement would
be pointed as being in an error of SQL syntax, while the same statement will
run fine if called from an ADO.Connection.


CurrentProject.Connection.Execute "CREATE TABLE ohLaLa (f1 LONG DEFAULT 0 )"
CurrentDb.Execute "CREATE TABLE ahLoLo (f1 LONG DEFAULT 0 )"



Vanderghast, Access MVP
 
Yes, these are JET 4 SQL extensions, but if your SQL statement
using them is called with a DAO.Database, it will fail, with an
error, the statement would be pointed as being in an error of SQL
syntax, while the same statement will run fine if called from an
ADO.Connection.
Click to expand...

But you weren't running SQL that altered any of the properties
accesible only through ADO.

Are you really suggesting that you use ADO all the time *just in
case* you need to run DDL that alters the properties not alterable
via DAO?
 
Nope, I mean that I use both, well, one at a time, but as it fits the moment
;-)

There are 'properties' that are accessible only by ADO, like a CHECK
constraint applied across records.

CurrentProject.Connection.Execute "ALTER TABLE playersTeams ADD CONSTRAINT
noMoreThanFourPlayersPerTeam CHECK( 4 >= (SELECT COUNT(*) FROM playersTeams
As a WHERE a.teamID=teamID)) "


should not allow you to add more than four rows for any given team number
(effectively making a kind of relation 1:4 between table teams and table
playersTeams) either by adding a new row, either by modifying an existing
one.

As far as I know, you cannot do it, with DAO, through SQL or otherwise.


Vanderghast, Access MVP
 
Back
Top