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]

Re: runsql vs Execute

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" <jrlowe3@hotmai l.com> wrote in message
news:70834f51.0 408291959.ec217 fd@posting.goog le.com...[color=blue]
> 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[/color]

[Jimmer]

Re: runsql vs Execute

Thanks.

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

Thanks again.

Jim.



"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message news:<4132a8e9$ 0$22819$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au >...[color=blue]
> 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" <jrlowe3@hotmai l.com> wrote in message
> news:70834f51.0 408291959.ec217 fd@posting.goog le.com...[color=green]
> > 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[/color][/color]

[Jimmer]

Re: runsql vs Execute

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

Thanks.



"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message news:<4132a8e9$ 0$22819$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au >...[color=blue]
> 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" <jrlowe3@hotmai l.com> wrote in message
> news:70834f51.0 408291959.ec217 fd@posting.goog le.com...[color=green]
> > 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[/color][/color]

[Allen Browne]

Re: runsql vs Execute

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" <jrlowe3@hotmai l.com> wrote in message
news:70834f51.0 408311009.2dcdd 77d@posting.goo gle.com...[color=blue]
> Thanks. I presume that with the exception of the setwarnings
> statement, the processing time is the same?
>
> Thanks.
>
>
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:<4132a8e9$ 0$22819$5a62ac2 2@per-qv1-newsreader-01.iinet.net.au >...[color=green]
>> 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" <jrlowe3@hotmai l.com> wrote in message
>> news:70834f51.0 408291959.ec217 fd@posting.goog le.com...[color=darkred]
>> > 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[/color][/color][/color]

[Tony Toews]

Re: runsql vs Execute

jrlowe3@hotmail .com (Jimmer) wrote:
[color=blue]
>Thanks. I presume that with the exception of the setwarnings
>statement, the processing time is the same?[/color]

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]

Re: runsql vs Execute

jrlowe3@hotmail .com (Jimmer) wrote in message news:<70834f51. 0408311009.2dcd d77d@posting.go ogle.com>...[color=blue]
> Thanks. I presume that with the exception of the setwarnings
> statement, the processing time is the same?
>
> Thanks.[/color]

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]

Re: runsql vs Execute

Jimmer wrote:
[color=blue]
> 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[/color]

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