INFO: Extracting Error Information from ADO in VB

• ActiveX Data Objects (ADO) included with: - Microsoft Visual Basic Professional and Enterprise Editions for

       Windows, versions 4.0, 5.0
  

SUMMARY

When ActiveX Data Objects (ADO) encounter an error, often the Errors Collection is filled with detail on the cause of the error. This article provides sample code for extracting the maximum possible information on any errors raised by ADO using Visual Basic.

If ADO itself encounters an error, it does not populate the Errors Collection, but instead you have to use a native error mechanism to catch and display the error--in this case the VB Err object. If the provider or underlying components generate error, then these will be populated in the ADO Errors Collection. So you need to check both the VB Error object and the ADO Errors Collection. However, you may want to preserve the values of the Err collection, because if you Error handling is too complex, in the process of examining and displaying the Errors collection, you could end up resetting the Err object.

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 VB 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. Below 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 KB 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
      Conn1.Close

      ' Destroy anything not destroyed yet
      Set Conn1 = Nothing

      ' We're outta here
      Exit Sub

   AdoError:

      Dim errLoop As Error
      Dim strError As String

      ' In case Conn1 isn't set or other initialization problems
      On Error Resume Next

      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

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q168354
   TITLE     : INFO: Underlying OLE and OLEDB Provider Errors
               Exposed via ADO

  ARTICLE-ID: Q168335
  TITLE     : HOWTO: Using ActiveX Data Objects Via Visual Basic

Keywords          : kbcode kberrmsg adoengdb adovb
Version           : 4.0 5.0
Platform          : WINDOWS
Issue type        : kbinfo