Error Handling in Access

Error Handling in Access

One of the advantages of using VBA code over macros is that you can have error handling. The end-users of your database are bound to try to do things that you hadn't thought of, with possibly disastrous results. Therefore, it is in your interest to try and firstly catch any expected errors, and secondly handle any unexpected errors in a clean way. For this reason, every procedure should have an error handler, even if it just traps the error. Below, I list a suggested layout for a procedure:

Public Sub sWhatever
    On Error GoTo E_Handle
    Dim db As Database
    Dim rs As Recordset
    Set db=DBEngine(0)(0)
    Set rs=db.OpenRecordset("tblName",dbOpenTable)
'   Various actions performed with the recordset
sExit:
    On Error Resume Next
    rs.Close
    Set rs=Nothing
    Set db=Nothing
    Exit Sub
E_Handle:
    Select Case Err.Number
        Case 12345
'   Expected error
        Case Else
'   Unexpected error
            MsgBox Err.Description, vbOKOnly,Err.Number
    End Select
    Resume sExit
End Sub

As well as having error handling here, I also have a single exit point for the procedure, so that whatever happens, the code should be tidied up, i.e. objects closed and set to nothing where appropriate. This has the advantage of avoiding a known bug in Access 97, where sometimes if references to recordset objects aren't closed and then destroyed (by setting equal to nothing) Access won't close, instead minimizing to the Task bar.

If the error handler ever displays an error number of 0, then it means that you have forgotten to put an Exit Sub/Function line in the code, and the code continues execution into the error handler regardless (An error value of 0 means no error).

There is an 'On Exit Resume Next' line in the Exit point, as there is the possibility that an error might occur prior to the Recordset being opened, and thus trying to close it in the Exit point causes an continuous loop. Unfortunately, DAO does not include a property for a recordset to check if it has been opened (I believe that ADO recordsets do, in the shape of the State property).

Top

Last modified at 06/06/2006 14:54:33