Question for Learning:  Docmd.OpenQuery versus db.Execute

Posted on 2006-03-28
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
    LVL 19

    Accepted Solution

    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
    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


    very good explanations.

    LVL 44

    Expert Comment

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

    Author Comment

    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

    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
    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now