Chapter 23: Handling Errors and Exceptions
837
This particular error-handling routine traps the errors on a bound form that occur when multiple
users make simultaneous changes to the same record. The Access database engine raises the error,
allowing the form to intelligently notify the user that a problem has occurred.
Notice how DataErr is examined to see if its value is 7787 or 7878 and an appropriate action
(notifying the user of the problem) is taken to handle the problem. Response is set to acData
ErrContinue to notify Access that the form’s data error has been handled.
If any other error occurs, Response is set to acDataErrDisplay, allowing Access to present
the user with the default error message. Hopefully, the user can make some sense of the error mes-
sage or at least notify someone of the situation.
Keep in mind that the form and report Error event fires only in response to data errors raised by
the database engine. The Error event is not related to problems caused by the user, other than
inappropriate data entry and a failure to add or update the wrong kind of data in the database.
The ADO Errors collection
When an error occurs in an ADO object, an Error object is created in the Errors collection of
the Connection object. These errors are referred to as data access errors. When an error occurs,
the collection is cleared and the new set of Error objects is put into the collection. Although the
collection exists only for the most recent data error event, a single event may generate several
errors. Each of these errors is stored in the Errors collection. The Errors collection is an object
of the Connection object, not ADO.
The Errors collection has one property, Count, which contains the number of errors or error
objects. It has a value of zero if there are no errors. There are a few properties of the Error object.
These include Description, HelpContext, HelpFile, Number, and Source. When there
are multiple errors, the lowest-level error is the first object in the collection, and the highest-level
error is the last object in the collection.
When an ADO error occurs, the VBA Err object contains the error number for the first object in
the Errors collection. You need to check the Errors collection to see whether additional ADO
errors have occurred.
In the following code, you find an error handler that can be used in a procedure that deals with an
ADO connection. When an error occurs, the code following the label HandleError runs and first
checks to see if the Error object contains any items. If it does, it checks to see if the error is the
same as the Err object. If it is the same, the error was an ADO error and strMessage contain
the descriptions of all the errors in the Errors collection. If it isn’t an ADO error, the error
occurred at the application level and the single Err.Description value is displayed:
Sub ADOTest()
Dim cnn As New ADODB.Connection
Dim errX As ADODB.Error
Dim strMessage As String
On Error GoTo HandleError