ADO Clear Method

ADO Clear Method

Removes all the Error objects from the Errors collection.

Errors.Clear

Applies To: Errors Collection (ADO)

Remarks:
Use the Clear method on the Errors collection to remove all existing Error objects from the collection. When an error occurs, ADO automatically clears the Errors collection and fills it with Error objects based on the new error.

Some properties and methods return warnings that appear as Error objects in the Errors collection but do not halt a program's execution. Before you call the Resync, UpdateBatch, or CancelBatch methods on a Recordset object; the Open method on a Connection object; or set the Filter property on a Recordset object, call the Clear method on the Errors collection. That way, you can read the Count property of the Errors collection to test for returned warnings.

Example:

' Execute, Requery, and Clear Methods Example
Public Sub Main()
    On Error GoTo Err_Execute

    ' connection, command, and recordset variables
    Dim Cnn As ADODB.Connection
    Dim cmdChange As ADODB.Command
    Dim rstTitles As ADODB.Recordset
    Dim Err As ADODB.Error
    Dim strSQLChange As String
    Dim strSQLRestore As String
    Dim strSQLTitles
    Dim strCnn As String
    
     ' 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=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\MyData\Northwind.mdb"
    Set Cnn = New ADODB.Connection
    Cnn.Open strCnn
    
     ' Create command object
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = Cnn
    cmdChange.CommandText = strSQLChange
    
     ' Open titles table
    Set rstTitles = New ADODB.Recordset
    strSQLTitles = "titles"
    rstTitles.Open strSQLTitles, Cnn, , , adCmdTable
    
    ' Print report of original data
    Debug.Print _
       "Data in Titles table before executing the query"
    PrintOutput rstTitles
    
    ' Call the ExecuteCommand subroutine below 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 and trap for
    ' errors, checking the Errors collection if necessary
    Cnn.Execute strSQLRestore, , adExecuteNoRecords
    
    ' Retrieve the current data by requerying the recordset
    rstTitles.Requery
    
    ' Print report of restored data using sub from below
    Debug.Print "Data after executing the query to restore the original information "
    PrintOutput rstTitles

    ' clean up
    rstTitles.Close
    Cnn.Close
    Set rstTitles = Nothing
    Set Cnn = Nothing
    Exit Sub
   
Err_Execute:
    ' Notify user of any errors that result from executing the query
    If rstTitles.ActiveConnection.Errors.Count >= 0 Then
       For Each Err In rstTitles.ActiveConnection.Errors
          MsgBox "Error number: " & Err.Number & vbCr & _
             Err.Description
       Next Err
    End If
   
    ' clean up
    If Not rstTitles Is Nothing Then
        If rstTitles.State = adStateOpen Then rstTitles.Close
    End If
    Set rstTitles = Nothing
    
    If Not Cnn Is Nothing Then
        If Cnn.State = adStateOpen Then Cnn.Close
    End If
    Set Cnn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub


Public Sub ExecuteCommand(cmdTemp As ADODB.Command, rstTemp As ADODB.Recordset)
   Dim Err As Error   
   ' Run the specified Command object and trap for errors, checking the Errors collection
   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 Err In rstTemp.ActiveConnection.Errors
         MsgBox "Error number: " & Err.Number & vbCr & _
            Err.Description
      Next Err
   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
'EndExecuteVB