Adit Cookbook Pages

Error Event Handling

You have to work quite hard to trap and control unexpected errors using Visual Basic version 6. You have to protect almost every routine with an “On Error GoTo” statement and insert an appropriate error handling routine into it.

It makes sense to have a standard piece of code you can just paste into each routine to cover most eventualities - you can always add any special code required by a given situation. Personally, I never use the error handling routine to trap errors such as “Divide by Zero” - I check the value in code. I leave my error routines as a last resort and more particularly for dealing with routine database locks.

So let us start with a few public constants and variables that can be used by the error routines.

Public Const RetryLimit = 4
Public Const TimeLimit = 15
Public LockTime As Variant
Public TryAgain As Long
Public Msg As String, title As String
Public errorResponse As Long
 

You might want to play with the RetryLimit and TimeLimit values when tuning a systems responses to database locks. Their purpose is to allow the error routine to ignore short term locks in your database while limiting the total waiting time in the event that such locks are not resolved. The problem is that the Jet database engine (and others based upon it) also react to the number of users being supported by a given database - slowing it’s response to locks as the database is loaded. As we are reasonably happy with an Access database supporting up to 50 simultanious users this can be a problem on occasion.

If you are writing a class then you may want to signal errors found to the client program - so you should add the following to your class declarations.

Public Const MyE2999 = 2999
Private ClassErrorCode As Long
Private ClassErrorSource As String
Private ClassErrorMessage As String
 

So here is the main error recovery routine for subs and functions that include database access.

   Select Case Err
       'Case vbObjectError + MyE2999
           'Abort selected in a function called from this sub or function
           'Exit Sub
       Case 3006, 3007, 3008, 3009, 3261, 3050
           If IsNull(LockTime) Then
               LockTime = Time
           End If
           TryAgain = TryAgain + 1
           If TryAgain > RetryLimit Or DateDiff("s", LockTime, Time) > TimeLimit Then
               title = "Database Lock"
               Msg = "A database or database page is currently locked."
               Msg = Msg & vbLf & "The details of the lock are shown below"
               Msg = Msg & vbLf & "Error Code = " & CStr(Err) & vbLf & Error$
               Msg = Msg & VBLF & " in Sub:"
               Screen.MousePointer = vbDefault
               errorResponse = MsgBox(Msg, vbExclamation + vbAbortRetryIgnore, title)
               Select Case errorResponse
                   Case vbAbort
                   Case vbRetry
                       Resume
                   Case vbIgnore
                       Resume Next
               End Select
               ResetLimits
           Else
               If Err = 3050 Then
                   DoEvents
               End If
               Resume
           End If
       Case Else
           Screen.MousePointer = vbDefault
           errorResponse = MsgBox("An unexpected error has occured." & vbLf & "Please note the details." & vbLf & Error(Err) & vbLf & "in Sub:", vbExclamation + vbAbortRetryIgnore, "Unexpected Error")
           Select Case errorResponse
               Case vbAbort
               Case vbRetry
                   Resume
               Case vbIgnore
                   Resume Next
           End Select
   End Select
   Screen.MousePointer = vbDefault
   'ClassErrorCode = MyE2999
   'ClassErrorMessage = Error$
   'Err.Raise Number:=ClassErrorCode + vbObjectError, Source:=ClassErrorSource, Description:=ClassErrorMessage
   Exit Sub
 

You can copy and paste this routine into your subroutines and functions and then just modify the two lines that identify the routine. If you are pasting it into a function then you may need to change any “Exit Sub” lines to “Exit Function”. If the routine is being copied into a routine in a class module then un-comment the three penultimate lines to let the calling routine know about unresolved errors. If you are pasting into a routine that calls code in a class then un-comment the line that starts “Case VBObjectError” and it’s associated “Exit Sub” as the user will already know about the problem and has elected to terminate the current activity.

On occasion you may want to re-set the time and lock counting variables so make a call to this routine which you should locate somewhere suitable.

Public Sub ResetLimits()
  
   LockTime = Null
   TryAgain = 0
  
End Sub
 

If there is no database activity in your routine then the whole thing can be simplified - I keep a copy of both versions available ready to paste into my code as required.

   'Error Recovery - no Database
   Select Case Err
       Case vbObjectError + MyE2999
           'Abort selected in a function called from this sub or function
           Exit Sub
       Case Else
           Screen.MousePointer = vbDefault
           errorResponse = MsgBox("An unexpected error has occured." & vbLf & "Please note details." & vbLf & Error(Err) & vbLf & "in Sub:", vbExclamation + vbAbortRetryIgnore, "Unexpected Error")
           Select Case errorResponse
               Case vbAbort
               Case vbRetry
                   Resume
               Case vbIgnore
                   Resume Next
           End Select
   End Select
   Screen.MousePointer = vbDefault
   'ClassErrorCode = MyE2999
   'ClassErrorMessage = Error$
   'Err.Raise Number:=ClassErrorCode + vbObjectError, Source:=ClassErrorSource, Description:=ClassErrorMessage
   Exit Sub