db.Execute vs DoCmd.RunSQL

[Kaseano]

Hey,
Someone on some post somewhere told me once to use db.Execute instead of
DoCmd.RunSQL because it would avoid those pop-ups that ask "are you sure?".

So I switched everything to db.Execute.
Now, with a few of the SQLs, I'll get an error message (when before, with
DoCmd instead of Execute, It worked fine).

One Example was this Query:

strSQL = "UPDATE [GPA Report Table] SET [GPA Report Table].Discount =
Forms!Pricing!bstxt WHERE ((([GPA Report Table].Carrier)='BELL SOUTH'));"


DoCmd.RunSQL strSQL works fine (other than the pop-up)

db.Execute strSQL returns:
Error #3061: Too few Parameters. Expected 1.

I was wondering if someone knew how to approach this.
For instance getting db.Execute to work for this SQL.
Also, I'm nervous that, under certain conditions this error might come back
on other queries when this program goes into circulation.

But... I don't want to turn off the warnings in order to use DoCmd.RunSQL

Any Advice would be Really appreciated.
Thanks

 

[Tony Toews [MVP]]

Someone on some post somewhere told me once to use db.Execute instead of
DoCmd.RunSQL because it would avoid those pop-ups that ask "are you sure?".

Could've been me.

Replace

strSQL = "UPDATE [GPA Report Table] SET [GPA Report Table].Discount =
Forms!Pricing!bstxt WHERE ((([GPA Report Table].Carrier)='BELL SOUTH'));"

with

strSQL = "UPDATE [GPA Report Table] SET [GPA Report Table].Discount =
" & Forms!Pricing!bstxt & " WHERE ((([GPA Report Table].Carrier)='BELL
SOUTH'));"

Also, I'm nervous that, under certain conditions this error might come back
on other queries when this program goes into circulation.

Once you fix these up you will be fine.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Phill H.]

You may want to try setting warnings to off before running the docmd and then
set warning to on after running the docmd.

 

[Allen Browne]

This article gives a summary of the difference between Exeucte and RunSQL:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Tony Toews [MVP]]

You may want to try setting warnings to off before running the docmd and then
set warning to on after running the docmd.

Not a good idea as this also turns off error messages.

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always 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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/