The Errors Collection

The Err object can handle one error at a time. For many beginning programmers, this object is an easy-to-use solution to error handling. However, as you get into more complex situations, involving use of the ADO libraries, a problem could cause multiple errors to occur. This means multiple error objects being created (programmers use the word “thrown”) at once. ADO has a means of collecting these objects and allowing the user to examine all of the errors returned.

The Err object is part of the VBA error-handling system and handles errors generated by VBA. The ADO error-handling system is separate from the VBA system and handles errors generated by the ADO objects. As a result, you must sometimes handle potential errors from both.

Since this is a beginning book, we will just take a brief look at the ADO Errors collection. The actual mechanics of using it are only slightly different from the Err object in the VBA system.

When we used the Err object, we did not need to implicitly declare it. It is built right into VBA. However, when using the Errors collection, you have to declare the object as you have done with any other object to this point.

Let’s create a code example in which we try to open a connection that doesn’t exist. In this example, we are going to throw an ADO exception that will be trapped and reported in the Immediate window:

Public Sub errorTest()   Dim myConn As ADODB.Connection   Dim myErr As ADODB.Error   Dim strError As String   On Error GoTo myHandler   ' Intentionally trigger an error   Set myConn = New ADODB.Connection   myConn.Open "nothing"   Set myConn = Nothing   Exit Sub myHandler:   ' Move through the Errors collection and display   ' properties of each Error object   For Each myErr In myConn.Errors   strError = "Error #" & Err.Number & vbCr & _   "  " & myErr.Description & vbCr & _   "  (Source: " & myErr.Source & ")" & vbCr & _   "  (SQL State: " & myErr.SQLState & ")" & vbCr & _   "  (NativeError: " & myErr.NativeError & ")" & vbCr   If myErr.HelpFile = "" Then   strError = strError & "  No Help file available"   Else   strError = strError & _   "  (HelpFile: " & myErr.HelpFile & ")" & vbCr & _   "  (HelpContext: " & myErr.HelpContext & ")" & _   vbCr & vbCr   End If   Debug.Print strError   Next   Resume Next End Sub

As I said at the outset, this code intentionally causes an error by opening a nonexistent connection. Let’s examine what is going on a piece at a time.

In the first few lines, you need to set up a reference to the Error object, as follows:

Dim myErr As ADODB.Error Dim strError As String 

Here the object reference is myErr, and it is of type Error found in the library ADODB. The String, strError, is going to be used in the event handler to build the error message.

A little farther down, we have the code:

Set myConn = New ADODB.Connection   myConn.Open "nothing"   Set myConn = Nothing

Essentially, this opens the connection to nothing and causes the error to be thrown intentionally for our demonstration. At that point, if all worked well, the sub procedure is exited. However, if an error is thrown (as it intentionally is here), control of the code is transferred to the label myHandler, which is the error handler.

At the beginning of the discussion, I mentioned that the advantage of the Errors collection is that it can collect multiple error objects. In this case, our handler needs to be able to move through those objects and return multiple messages if needed. Since myConn caused the error to happen, it serves as the container to hold the collection. For that reason, in the event handler, we set up the following loop specifications:

For Each myErr In myConn.Errors

Recall from Chapter 5 that an array is a collection of data referenced by a single variable name. Here, we have a slight variation. We have a collection of objects, located within myConn, referenced by one object reference named myErr. This loop will keep traversing through it until it has queried all the objects.

Once in the loop, we now use our String variable to set up a message string for printing in the Immediate window, as follows:

strError = "Error #" & Err.Number & vbCr & _   "  " & myErr.Description & vbCr & _   "  (Source: " & myErr.Source & ")" & vbCr & _   "  (SQL State: " & myErr.SQLState & ")" & vbCr & _   "  (NativeError: " & myErr.NativeError & ")" & vbCr

This string contains each of the properties of the Error object.

We see the properties Number, Description, and Source, which mimic the Err object seen earlier. The SQLState property will contain errors returned by the database server within your SQL string syntax.

The NativeError property returns error codes generated by the providing server.

Although I didn’t speak about it before, one feature of both the Err and the Error objects is the ability to access customized help files within the context of the error message number. You could specify the location and name of the help file with the HelpFile and HelpContext properties (in many ways, I treat these as one property). In our example, we are not going to use these properties. We can specify that by using an empty string. As you can see, in the following excerpt from the preceding code, I put it in the context of an If structure:

If myErr.HelpFile = "" Then   strError = strError & "  No Help file available"   Else   strError = strError & _   "  (HelpFile: " & myErr.HelpFile & ")" & vbCr & _   "  (HelpContext: " & myErr.HelpContext & ")" & _   vbCr & vbCr   End If

This code example produces an error. As a result, the output in the Immediate window should look like this:

Note

vbCR is an intrinsic constant of Visual Basic (as identified by the vb prefix) and returns a carriage return.