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.
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.
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 Sub3. Press F5 and run the program, then click on the Error Collection button.
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.
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.OpenWhen 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.