Execute, Requery, and Clear Methods Example (VB)

Execute, Requery, and Clear Methods Example (VB)

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a Recordset, and the Clear method to clear the contents of the Errors collection. (The Errors collection is accessed via the Connection object of the ActiveConnection property of the Recordset.) The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

Public Sub ExecuteX()

    Dim strSQLChange As String
    Dim strSQLRestore As String
    Dim strCnn As String
    Dim cnn1 As ADODB.Connection
    Dim cmdChange As ADODB.Command
    Dim rstTitles As ADODB.Recordset
    Dim errLoop As ADODB.Error

    ' Define two SQL statements to execute as command text.
    strSQLChange = "UPDATE Titles SET Type = " & _
        "'self_help' WHERE Type = 'psychology'"
    strSQLRestore = "UPDATE Titles SET Type = " & _
        "'psychology' WHERE Type = 'self_help'"

    ' Open connection.
        strCnn = "Provider=sqloledb;" & _
        "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
    Set cnn1 = New ADODB.Connection
    cnn1.Open strCnn

    ' Create command object.
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = cnn1
    cmdChange.CommandText = strSQLChange
    
    ' Open titles table.
    Set rstTitles = New ADODB.Recordset
    rstTitles.Open "titles", cnn1, , , adCmdTable

    ' Print report of original data.
    Debug.Print _
        "Data in Titles table before executing the query"
    PrintOutput rstTitles

    ' Clear extraneous errors from the Errors collection.
    cnn1.Errors.Clear

    ' Call the ExecuteCommand subroutine to execute cmdChange command.
    ExecuteCommand cmdChange, rstTitles
    
    ' Print report of new data.
    Debug.Print _
        "Data in Titles table after executing the query"
    PrintOutput rstTitles

    ' Use the Connection object's execute method to 
    ' execute SQL statement to restore data. Trap for 
    ' errors, checking the Errors collection if necessary.
    On Error GoTo Err_Execute
    cnn1.Execute strSQLRestore, , adExecuteNoRecords
    On Error GoTo 0

    ' Retrieve the current data by requerying the recordset.
    rstTitles.Requery

    ' Print report of restored data.
    Debug.Print "Data after executing the query " & _
        "to restore the original information"
    PrintOutput rstTitles

    rstTitles.Close
    cnn1.Close
    
    Exit Sub
    
Err_Execute:

    ' Notify user of any errors that result from
    ' executing the query.
    If rstTitles.ActiveConnection.Errors.Count >= 0 Then
        For Each errLoop In rstTitles.ActiveConnection.Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                errLoop.Description
        Next errLoop
    End If
    Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
    rstTemp As ADODB.Recordset)

    Dim errLoop As Error
    
    ' Run the specified Command object. Trap for 
    ' errors, checking the Errors collection if necessary.
    On Error GoTo Err_Execute
    cmdTemp.Execute
    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 rstTemp.ActiveConnection.Errors.Count > 0 Then
        For Each errLoop In Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                errLoop.Description
        Next errLoop
    End If
    
    Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

    ' Enumerate Recordset.
    Do While Not rstTemp.EOF
        Debug.Print "  " & rstTemp!Title & _
            ", " & rstTemp!Type
        rstTemp.MoveNext
    Loop

End Sub