Reacquaint Yourself with the Object Browser

Earlier in this book we touched on the Object Browser. Well, this is a very handy tool to use for becoming familiar with the various members of the ADODB model.  Take a minute and select View | Object Browser from the main VB 6.0 IDE window. Select ADODB from the drop down window and take a look around. This will be time well spent in becoming familiar with all of the members of ADODB:

Let's walk through what some of the information displayed here is all about.

The ADO Errors Collection

One thing we need to learn about is the Errors collection. When an error is encountered by ADO, the Errors collection is filled with detail on the culprit.  Depending on the source of the error, or even if there are bugs in the underlying OLE DB provider to ADO, the Errors collection may not be populated. But for the most part, VB will tell you the cause of the problem.  The Errors collection is available only from the connection object.

Let's take a look at how we can access the information that's held in the Errors collection.

Try It Out – Harvesting Errors from the Errors Collection

1.   Add another command button to your frmSchema form and name it cmdErrors. Add an Error Collection caption so that it looks like this:

2.  Add this code to the click event procedure of the cmdErrors button:

Private Sub cmdErrors_Click()

Dim adoConnection As ADODB.Connection
Dim adoErrors As ADODB.Errors

Dim i As Integer
Dim StrTmp

On Error GoTo AdoError

Set adoConnection = New ADODB.Connection

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

adoConnection.Open

' Remaining code goes here, but of course our program' will never reach it because the connection string' will generate an error because of the bogus directory' Close the open objects
adoConnection.Close

' Destroy anything not destroyed yet
Set adoConnection = Nothing

Exit Sub

AdoError:

  Dim errorCollection As Variant
  Dim errLoop As Error
  Dim strError As String
  Dim iCounter As Integer
  
 ' In case our adoConnection is not set or ' there were other initialization problems
  On Error Resume Next

  iCounter = 1

  ' Enumerate Errors collection and display properties of  ' each Error object.
   strError = ""
   Set errorCollection = adoConnection.Errors
   For Each errLoop In errorCollection
         With errLoop
            strError = "Error #" & iCounter & vbCrLf
            strError = strError & " ADO Error #" & .Number & vbCrLf
            strError = strError & " Description  " & .Description & vbCrLf
            strError = strError & " Source       " & .Source & vbCrLf
            Debug.Print strError
            iCounter = iCounter + 1
         End With
      Next
     
End Sub

3.  Press F5 and run the program, then click on the Error Collection button.

The Errors Collection Output

We didn't place these in screen shots of the immediate window because the output is actually longer than the screen. The error messages have gone from being rather terse to chatty Cathy in nature. They now are almost conversational. Here is what you will see in the immediate window:

Error #1

 ADO Error #-2147467259

 Description  [Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)' isn't 

       a valid path.  Make sure that the path name is spelled correctly and

      that you are connected to the server on which the file resides.

 Source       Microsoft OLE DB Provider for ODBC Drivers

Error #2

 ADO Error #-2147467259

 Description  [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr

       failed

 Source       Microsoft OLE DB Provider for ODBC Drivers

Now that's handy. Instead of some strange number, the ADO errors are more 'wordy' - and they even look like English. This is more like it!

Let's have a look at how this all fits together.

How It Works

We first want to set up an error handler to trap and skin any errors that may occur in our program:

On Error GoTo AdoError

When the code hits this line, our local error handler, AdoError, becomes active. From this point forward, when an error occurs in our procedure, control automatically jumps to the label AdoError that contains our handler.

Our connection string is then defined.

adoConnection.ConnectionString = "DBQ=BIBLIO.MDB;" & _
                  "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                  "DefaultDir=C:\OhNooo\Directory\Path;"

Of course, we don't have a directory called C:\OhNooo\Directory\Path so the connection object will not be able to communicate with the database.

Simply setting the bogus connection string does not cause the error. But when we invoke the .Open method with the faulty string, this does cause our problem and generates the error:

adoConnection.Open

When our program can't establish a connection with the database, an error is generated. And since we have an active error handler, the code jumps there immediately.

Once in our error handler, we can then loop through the error collection of our adoConnection object. And each error provides us with a Number, a Description of the error, and the source of the error. Very handy, and a lot of useful descriptive information:

Set errorCollection = adoConnection.Errors
   For Each errLoop In errorCollection
         With errLoop
            strError = "Error #" & iCounter & vbCrLf
            strError = strError & " ADO Error #" & .Number & vbCrLf
            strError = strError & " Description  " & .Description & vbCrLf
            strError = strError & " Source       " & .Source & vbCrLf
            Debug.Print strError
            iCounter = iCounter + 1
         End With
      Next

We route each error to the immediate window using our trusty debug.print.  This is the result of attempting to connect with a database that resides in a bogus path. Notice that the first error is smart enough to know that the problem is a bad path! It not only tells us the problem, but is polite enough to suggest what we should do about it:

Error #1

 ADO Error #-2147467259

 Description  [Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)' isn't 

       a valid path.  Make sure that the path name is spelled correctly and

      that you are connected to the server on which the file resides.

 Source       Microsoft OLE DB Provider for ODBC Drivers

Next, we'll see how we can have a look at how we can display some information about the Data Provider itself.

© 1998 by Wrox Press. All rights reserved.