runsql vs Execute

[Jimmer]

What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
to run sql? When is one better than the other or inappropriate to use?

Thanks.

jim

[Allen Browne]

Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jimmer" <jr*****@hotmail.com> wrote in message
news:70*************************@posting.google.co m...

What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
to run sql? When is one better than the other or inappropriate to use?

Thanks.

jim

[Jimmer]

Thanks.

I presume that excluding the Setwarnings statement. The execution time is the same?

Thanks again.

Jim.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jimmer" <jr*****@hotmail.com> wrote in message
news:70*************************@posting.google.co m...

What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
to run sql? When is one better than the other or inappropriate to use?

Thanks.

jim

[Jimmer]

Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem. Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jimmer" <jr*****@hotmail.com> wrote in message
news:70*************************@posting.google.co m...

What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
to run sql? When is one better than the other or inappropriate to use?

Thanks.

jim

[Allen Browne]

Try it and see.

I doubt there will be much difference. Other factors are likely to loom
larger, such as whether the data is in cache, whether you are using
transactions, network traffic (if the data is on a server), ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jimmer" <jr*****@hotmail.com> wrote in message
news:70**************************@posting.google.c om...

Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?

Thanks.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...

Execute (DAO) has several advantages, e.g.:
- Does not need you to turn off SetWarnings.
- The dbFailOnError switch lets you know if it worked or not;
- You can use it with transactions, to roll back after a problem.
Example:
http://members.iinet.net.au/~allenbrowne/ser-37.html

"Jimmer" <jr*****@hotmail.com> wrote in message
news:70*************************@posting.google.co m...

> What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
> to run sql? When is one better than the other or inappropriate to use?
>
> Thanks.
>
> jim

[Tony Toews]

jr*****@hotmail.com (Jimmer) wrote:

Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?

One person stated in testing one particular update that Execute took 2 seconds while
docmd.runsql took 8 seconds. YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Pieter Linden]

jr*****@hotmail.com (Jimmer) wrote in message news:<70**************************@posting.google. com>...

Thanks. I presume that with the exception of the setwarnings
statement, the processing time is the same?

Thanks.

No idea. Put a timer on your form and execute each one something like
10,000 times in a loop. Use GetTickCount and you'll have your answer.

[Trevor Best]

Jimmer wrote:

What's better/ faster/ more efficient. Using Docmd.RunSql or Execute
to run sql? When is one better than the other or inappropriate to use?

Thanks.

jim

RunSQL gives you nice progress meter, if the query is not very complex
then it's quite accurate :-)

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\