QueryDef.Execute Method (DAO)

Office 2013 and later

Last modified: March 09, 2015

Applies to: Access 2013 | Office 2013

In this article
Syntax
Remarks
Example
About the Contributors

Executes an SQL statement on the specified object.

expression .Execute(Options)

expression A variable that represents a QueryDef object.

Parameters

Name

Required/Optional

Data Type

Description

Options

Optional

Variant

You can use the following RecordsetOptionEnum constants for Options.

Constant

Description

dbDenyWrite

Denies write permission to other users (Microsoft Access workspaces only).

dbInconsistent

(Default) Executes inconsistent updates (Microsoft Access workspaces only).

dbConsistent

Executes consistent updates (Microsoft Access workspaces only).

dbSQLPassThrough

Executes an SQL pass-through query. Setting this option passes the SQL statement to an ODBC database for processing (Microsoft Access workspaces only).

dbFailOnError

Rolls back updates if an error occurs (Microsoft Access workspaces only).

dbSeeChanges

Generates a run-time error if another user is changing data you are editing (Microsoft Access workspaces only).

dbRunAsync

Executes the query asynchronously (ODBCDirect Connection and QueryDef objects only).

dbExecDirect

Executes the statement without first calling SQLPrepare ODBC API function (ODBCDirect Connection and QueryDef objects only).

Note Note

ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.

Note Note

The constants dbConsistent and dbInconsistent are mutually exclusive. You can use one or the other, but not both in a given instance of OpenRecordset. Using both dbConsistent and dbInconsistent causes an error.

Use the RecordsAffected property of the Connection, Database, or QueryDef object to determine the number of records affected by the most recent Execute method. For example, RecordsAffected contains the number of records deleted, updated, or inserted when executing an action query. When you use the Execute method to run a query, the RecordsAffected property of the QueryDef object is set to the number of records affected.

In a Microsoft Access workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. 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.

In earlier versions of the Microsoft Jet database engine, SQL statements were automatically embedded in implicit transactions. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. To improve performance, these implicit transactions were removed starting with version 3.5. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.

For best performance in a Microsoft Access workspace, especially in a multiuser environment, nest the Execute method inside a transaction. Use the BeginTrans method on the current Workspace object, then use the Execute method, and complete the transaction by using the CommitTrans method on the Workspace. This saves changes on disk and frees any locks placed while the query is running.

This example demonstrates the Execute method when run from both a QueryDef object and a Database object. The ExecuteQueryDef and PrintOutput procedures are required for this procedure to run.

Sub ExecuteX() 
 
   Dim dbsNorthwind As Database 
   Dim strSQLChange As String 
   Dim strSQLRestore As String 
   Dim qdfChange As QueryDef 
   Dim rstEmployees As Recordset 
   Dim errLoop As Error 
 
   ' Define two SQL statements for action queries. 
   strSQLChange = "UPDATE Employees SET Country = " & _ 
      "'United States' WHERE Country = 'USA'" 
   strSQLRestore = "UPDATE Employees SET Country = " & _ 
      "'USA' WHERE Country = 'United States'" 
 
   Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
   ' Create temporary QueryDef object. 
   Set qdfChange = dbsNorthwind.CreateQueryDef("", _ 
      strSQLChange) 
   Set rstEmployees = dbsNorthwind.OpenRecordset( _ 
      "SELECT LastName, Country FROM Employees", _ 
      dbOpenForwardOnly) 
 
   ' Print report of original data. 
   Debug.Print _ 
      "Data in Employees table before executing the query" 
   PrintOutput rstEmployees 
    
   ' Run temporary QueryDef. 
   ExecuteQueryDef qdfChange, rstEmployees 
    
   ' Print report of new data. 
   Debug.Print _ 
      "Data in Employees table after executing the query" 
   PrintOutput rstEmployees 
 
   ' Run action query to restore data. Trap for errors, 
   ' checking the Errors collection if necessary. 
   On Error GoTo Err_Execute 
   dbsNorthwind.Execute strSQLRestore, dbFailOnError 
   On Error GoTo 0 
 
   ' Retrieve the current data by requerying the recordset. 
   rstEmployees.Requery 
 
   ' Print report of restored data. 
   Debug.Print "Data after executing the query " & _ 
      "to restore the original information" 
   PrintOutput rstEmployees 
 
   rstEmployees.Close 
    
   Exit Sub 
    
Err_Execute: 
 
   ' Notify user of any errors that result from 
   ' executing the query. 
   If DBEngine.Errors.Count > 0 Then 
      For Each errLoop In DBEngine.Errors 
         MsgBox "Error number: " & errLoop.Number & vbCr & _ 
            errLoop.Description 
      Next errLoop 
   End If 
    
   Resume Next 
 
End Sub 
 
Sub ExecuteQueryDef(qdfTemp As QueryDef, _ 
   rstTemp As Recordset) 
 
   Dim errLoop As Error 
    
   ' Run the specified QueryDef object. Trap for errors, 
   ' checking the Errors collection if necessary. 
   On Error GoTo Err_Execute 
   qdfTemp.Execute dbFailOnError 
   On Error GoTo 0 
 
   ' Retrieve the current data by requerying the recordset. 
   rstTemp.Requery 
    
   Exit Sub 
 
Err_Execute: 
 
   ' Notify user of any errors that result from 
   ' executing the query. 
   If DBEngine.Errors.Count > 0 Then 
      For Each errLoop In DBEngine.Errors 
         MsgBox "Error number: " & errLoop.Number & vbCr & _ 
            errLoop.Description 
      Next errLoop 
   End If 
    
   Resume Next 
 
End Sub 
 
Sub PrintOutput(rstTemp As Recordset) 
 
   ' Enumerate Recordset. 
   Do While Not rstTemp.EOF 
      Debug.Print "  " & rstTemp!LastName & _ 
         ", " & rstTemp!Country 
      rstTemp.MoveNext 
   Loop 
 
End Sub 

The following example shows how to execute a parameter query. The Parameters collection is used to set the Organization parameter of the myActionQuery query before the query is executed.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors

Public Sub ExecParameterQuery()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("myActionQuery")

    'Set the value of the QueryDef's parameter
    qdf.Parameters("Organization").Value = "Microsoft"

    'Execute the query
    qdf.Execute dbFailOnError

    'Clean up
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing

End Sub

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.

Show: