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?

> 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.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

"Albert D.Kallal" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
>
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

>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 wrote:
>>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.
>
>

> 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..........


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal