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