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