Execute Method

Applies To Connection object, Database object, QueryDef object.

Description

Runs an action query or executes an SQL statement on a specified Connection or Database object.

Syntax

object.Execute source, options

querydef.Execute options

The Execute method syntax has these parts.

Part	Description

object	A Connection or Database object variable on which the query will run.
querydef	An object variable that represents the QueryDef object whose SQL property setting specifies the SQL statement to execute.
source	A String that is an SQL statement or the Name property value of a QueryDef object.
options	Optional. A constant or combination of constants that determines the data integrity characteristics of the query, as specified in Settings.

Settings

You can use the following constants for options.

Constant	Description

dbDenyWrite	Denies write permission to other users (Microsoft Jet workspaces only).
dbInconsistent	(Default) Executes inconsistent updates (Microsoft Jet workspaces only).
dbConsistent	Executes consistent updates (Microsoft Jet workspaces only).
dbSQLPassThrough	Executes an SQL pass-through query. Setting this option passes the SQL statement to an ODBC database for processing (Microsoft Jet workspaces only).
dbFailOnError	Rolls back updates if an error occurs (Microsoft Jet workspaces only).
dbSeeChanges	Generates a run-time error if another user is changing data you are editing (Microsoft Jet 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 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.

Remarks

The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs. Because an action query doesn't return any records, Execute doesn't return a Recordset. (Executing an SQL pass-through query in an ODBCDirect workspace will not return an error if a Recordset isn't returned.)

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

For best performance in a Microsoft Jet 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.

In an ODBCDirect workspace, if you include the optional dbRunAsync constant, the query runs asynchronously. To determine whether an asynchronous query is still executing, check the value of the StillExecuting property on the object from which the Execute method was called. To terminate execution of an asynchronous Execute method call, use the Cancel method.

See Also Cancel method, RecordsAffected property, StillExecuting property.

Example

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

Example (Microsoft Access)

The following example executes an action query and prints the number of records affected:

Sub RecordsUpdated()
    Dim dbs As Database, qdf As QueryDef
    Dim strSQL As String
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = "UPDATE Employees SET Title = " _
        & "'Senior Sales Representative' " _
        & "WHERE Title = 'Sales Representative';"
    ' Create new QueryDef.
    Set qdf = dbs.CreateQueryDef("UpdateTitles", strSQL)
    ' Execute QueryDef.
    qdf.Execute
    Debug.Print qdf.RecordsAffected
    Set dbs = Nothing
End Sub