Question for Learning: Docmd.OpenQuery versus db.Execute

I've got something bugging me, so I thought I'd throw it to the Gurus for some thoughts/advice:

I recently have been trying to incorporating the DAO execute method.  It seems that for action queries, the idea that "this option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted" (from microsoft help) is appealing.

Is the execute method "better" than the openquery method?  I sometimes get errors when attempting to run queries with the Execute Method, such as "Too Few Parameters -- Expecting 2" or "Overflow".  When I change the line of code from db.execute back to docmd.openquery, the error goes away.  I don't know enough about the execute method to know how to troubleshoot, so I abandon the idea of using it.

When is the best time to use either method?  Is one method preferable over another?

Thanks for your insight.

;-)
Ronda-SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dmitryz6Commented:
If you are using DAO better to use
querydefinition object,Easy to debug

ex

Dim db As DAO.Database, qdef1 As DAO.QueryDef
Set db = CurrentDb
         Set qdef1 = db.QueryDefs("YourQuery")
         qdef1.Parameters(0) = forms!FormName!textboxname
         qdef2.Execute dbFailOnError

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
Docmd.OpenQuery is an Access method.
(Docmd is an object of the Access application)
It allows use of Access' expression service to evaluate the parameters that appear in your query - just as Access does under UI use of your query.

CurrentDb is returning a DAO database reference (Access is returning it - it's true - but it is none the less not an Access object).  It is not able to use the expression service - hence you need to then use querydef code along the lines of that which Dmitry suggests.
0
dmitryz6Commented:
Leigh.

very good explanations.

Regards
  Dmitry
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Leigh PurvisDatabase DeveloperCommented:
Glad you liked them partner - hopefully the questioner will find them useful. :-)
0
Ronda-SAuthor Commented:
Thank you for taking the time to respond to this question -- I have been programming in Access for around 7 years now, and this site has been invaluable to me, both as a problem solver and as a learning tool.

I have not been using the querydef reference when trying the db.execute.  I will  try that.

One last question, though -- would you two consider one method superior over the other?  Why would I want to use db.execute over docmd.openquery??  Am I correct in the rollback advantage?

Thanks again,

Ronda
0
dmitryz6Commented:
Thank you for points.

As Leigh mention docmd.openquery it is prebuild Access Method.You could not use roll back on access functions just on DAO or ADO or ...
0
Leigh PurvisDatabase DeveloperCommented:
You'd use a querydef *instead* of db.execute - because the querydef allows you to define the parameters.
If all you're looking to do is run an update SQL statement - then opening a DAO querydef seems a little unnecessary.

docmd.openquery will use Access expressions - but will ask for confirmation first (requiring Setwarnings to be set first to prevent it).
db.execute won't prompt - but at the same time won't use those expressions mentioned.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.