By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,829 Members | 670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,829 IT Pros & Developers. It's quick & easy.

runsql vs Execute

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

P: n/a
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.
Nov 13 '05 #7

P: n/a
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.
\__\

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.