ACC2000 - DoCmd.RunSQL vs CurrentDB.Execute

[Guest]

I have taken over an Access 2000 MDB that has linked tables to a SQL-Server
database, and there are a bunch of "DoCmd.RunSQL" and "CurrentDb.Execute"
statements throughout the VBA code. I wanted to clean this up and have more
consistency throughout.

Is there any performance gains from using "DoCmd.RunSQL" compared to
"CurrentDB.Execute" when linked SQL-Server tables are involved?

 

[Albert D.Kallal]

Is there any performance gains from using "DoCmd.RunSQL" compared to

"CurrentDB.Execute" when linked SQL-Server tables are involved?

For the most part, no. The *better* choice is to use

currentdb.Execute

The above is clanear, does not rely on the "setwarnings" command, and also
does not wrap the sql in a transaction (however, in your case of linked odbc
tables..this would not make a differnce).

The 2nd and more critical issue is that currentdb is a method of the JET
database engine, and NOT that of ms-access. This means that forms
expressions, and VB function names can NOT be used when you use
currentdb.Execute, but YOU CAN use VBA and even forms!myform!myfield name
expressions when you use the docmd.

I as a general rule always use currentdb.Execute, unless for some reason I
need a VBA functions or expression in the sql. In those cases, you MUST use
docmd.runsql.

Since currentdb forces one to use cleaner sql, and not use VB expressions,
then that would also likely be a better choice for linked tables to sql
server, but only because less ms-access features are being used, and you
have a better chance of the sql being sent to sql server will less
modifications, or jet needing to pull less data from the server to do its
job.

 

[Dirk Goldgar]

The 2nd and more critical issue is that currentdb is a method of the
JET database engine, and NOT that of ms-access. This means that forms
expressions, and VB function names can NOT be used when you use
currentdb.Execute, but YOU CAN use VBA and even forms!myform!myfield
name expressions when you use the docmd.

I think you're mistaken, Albert, or I'm not understanding you properly.
You *can* use VBA functions in queries executed by the DAO Execute
method, so long as the query is being executed from within Access. For
example, from code inside an Access application, I can write

CurrentDb.Execute _
"UPDATE Table1 SET [TextField] = Mid([textField],6)"

or even

CurrentDb.Execute _
"UPDATE Table1 SET [TextField] = MyCustomFunction([textField])"

But from any other application that just uses the Jet engine, I can't do
it. For example in an application developed with Visual Basic -- not
Access -- I can't run this query:

Set db = DBEngine.OpenDatabase("MyDB.mdb")

'*** Won't work
db.Execute _
"UPDATE Table1 SET [TextField] = MyCustomFunction([textField])"

I don't think this query will work, either:

' *** Will this work?
db.Execute _
"UPDATE Table1 SET [TextField] = Mid([textField],6)"

But I'm not completely sure about that.

What you can't do with the Execute method is use queries that refer to
controls on forms -- *unless* you fill those values in as parameters
before executing. I use a QueryDef object and its Parameters collection
to accomplish this, when I have to.

 

[David C. Holley]

I as a general rule always use currentdb.Execute, unless for some

reason I need a VBA functions or expression in the sql. In those

cases, >you MUST use docmd.runsql.

Or build the SQL statement using a variable and place that in the
..Execute statement.

 

[Albert D.Kallal]

I think you're mistaken, Albert, or I'm not understanding you properly.

You *can* use VBA functions in queries executed by the DAO Execute
method, so long as the query is being executed from within Access. For
example, from code inside an Access application, I can write

CurrentDb.Execute _
"UPDATE Table1 SET [TextField] = Mid([textField],6)"

or even

CurrentDb.Execute _
"UPDATE Table1 SET [TextField] = MyCustomFunction([textField])"

But from any other application that just uses the Jet engine, I can't do
it. For example in an application developed with Visual Basic -- not
Access -- I can't run this query:

Well,...yes...I am incorrect on this!!..... My thinking is perhaps jaded due
to working with the dao object in other cases..........