RunSQL vs Execute

[justin]

I've built a SELECT...INTO SQL statement using VBA called strSQL, and I'm
wondering why

DoCmd.RunSQL (strSQL)

returns an error while

db.QueryDefs("MyQuery").SQL = strSQL
db.QueryDefs("MyQuery").Execute

works the way I want it too. After reading Access 2000's help file, it
seems that they should both accomplish the same goal. What am I missing?

- Justin

[Tony Oakley]

justin wrote:

Don't know answer to your question. But I believe that DoCmd.RunSQL is
depreicated in favour of db.Execute anyway. By the way, your can execute your
query as follows too:

db.Execute strSQL

Hope this helps

Tony Oakley

[SteveT]

And db.execute strSql runs faster also.

You should add the optional switch dbFailOnError. If you don't and there is an error in your code (wrong fields, etc) the code will fail without a error you can trap.
So use:

db.Execute strSql, dbFailOnError

Steve

Tony Oakley <To...@nospam.softoak.demon.co.uk> wrote in message news:3AFFE157...@nospam.softoak.demon.co.uk...

[justin]

<SteveT> wrote in message news:#lBRSFJ3AHA.2192@tkmsftngp07...

And db.execute strSql runs faster also.

You should add the optional switch dbFailOnError. If you don't and there is
an error in your code (wrong fields, etc) the code will fail without a error
you can trap.
So use:

db.Execute strSql, dbFailOnError

-------
Thanks guys for the input. I knew there had to be difference and any pickup
in speed that I can get is a good difference.

- Justin

[Allen Browne]

The Execute method has 4 very significant benefits:

1. Executes faster.

2. dbFailOnError lets you know if anything went wrong.

3. RecordsAffected lets you know how many records were changed.

4. Can be used in a transaction (to rollback).

(Note that #2 does not imply #4. Without the transaction,
dbFailOnError just quits trying at the point where it first fails,
resulting in a job that's partly done. This fact applies only to
Access 97 onwards.)

--
Perth, Western Australia
Tips for MS Access users at:
http://odyssey.apana.org.au/~abrowne