INFO: Extracting Error Information from ADO in VB

MORE INFORMATION

The documentation for the ADO Error object indicates that the Errors Collection will be populated if any error occurs within ADO or it's underlying provider. This is somewhat incorrect. Depending on the source of the error, or even bug, in the underlying provider to ADO (OLE-DB) or within ADO itself, the errors collection may not be populated. You need to check both the Visual Basic Error object as well as the ADO Errors collection.

The Errors Collection is only available from the Connection object, so you need to initialize ADO off of a Connection object. Following is sample code that demonstrates how to open a connection and report any errors encountered.

Often the Errors Collection returns an HRESULT in either hexadecimal format (for example, 0x80004005) or as a long value (for example, 2147467259). These HRESULTS can be raised by underlying components such as OLE-DB or even OLE itself. When this is the case, it may be confusing since these codes are not documented in the ADO online documentation. However, frequently encountered HRESULTS can be found in the Microsoft Knowledge Base article listed in the REFERENCES section.

Private Sub cmdTemplate_Click()

   Dim Conn1 As Connection
   Dim Errs1 As Errors
   Dim i As Integer
   Dim StrTmp

   On Error GoTo AdoError

   Set Conn1 = CreateObject("ADODB.Connection")

   ' Open connection to Bogus ODBC Data Source for BIBLIO.MDB
   Conn1.ConnectionString = "DBQ=BIBLIO.MDB;" & _
              "DRIVER={Microsoft Access Driver (*.mdb)};" & _
              "DefaultDir=C:\Bogus\Directory\Path;" & _
              "UID=admin;PWD=;"
   Conn1.Open

   ' Remaining code goes here.

   Done:

   ' Close all open objects.
     If Conn1.State = adStateOpen Then
        Conn1.Close
      End If

   ' Destroy anything not destroyed yet.
     Set Conn1 = Nothing

   ' We're done.
     Exit Sub

   AdoError:

      Dim errLoop As Error
      Dim strError As String

      i = 1

   ' Process
     StrTmp = StrTmp & vbCrLf & "VB Error # " & Str(Err.Number)
     StrTmp = StrTmp & vbCrLf & "   Generated by " & Err.Source
     StrTmp = StrTmp & vbCrLf & "   Description  " & Err.Description

   ' Enumerate Errors collection and display properties of
   ' each Error object.
     Set Errs1 = Conn1.Errors
     For Each errLoop In Errs1
          With errLoop
            StrTmp = StrTmp & vbCrLf & "Error #" & i & ":"
            StrTmp = StrTmp & vbCrLf & "   ADO Error   #" & .Number
            StrTmp = StrTmp & vbCrLf & "   Description  " & .Description
            StrTmp = StrTmp & vbCrLf & "   Source       " & .Source
            i = i + 1
       End With
    Next

      MsgBox StrTmp

      ' Clean up Gracefully

      On Error Resume Next
      GoTo Done

   End Sub