[Last Call] Learn how to a build a cloud-first strategyRegister Now


Question for Learning:  Docmd.OpenQuery versus db.Execute

Posted on 2006-03-28
Medium Priority
Last Modified: 2008-02-26
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.

Question by:Ronda-S
  • 3
  • 3
LVL 19

Accepted Solution

dmitryz6 earned 800 total points
ID: 16315822
If you are using DAO better to use
querydefinition object,Easy to debug


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

LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 800 total points
ID: 16316576
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.
LVL 19

Expert Comment

ID: 16316652

very good explanations.

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 44

Expert Comment

by:Leigh Purvis
ID: 16316688
Glad you liked them partner - hopefully the questioner will find them useful. :-)

Author Comment

ID: 16334222
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,

LVL 19

Expert Comment

ID: 16334901
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 ...
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16336933
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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question