An Access Error Log
Maintenance and debugging VBA code is very much easier if you have a
reliable record of exactly what went wrong. Once your database has
been released into everyday use you will find yourself relying on the
user's memory for reports of errors and details of error messages.
Write yourself an error log so that you have an accurate and
independent record of what really happened.
Error Log
A typical entry in our log file looks like this:
[23/09/2005],[15:55:49],[Backup failed],[53],[File not found]
We chose to use a simple text file so that we could read it from a
user's PC without needing anything more sophisticated than
NotePad. The regular format of fields delimited by [ & ] and
separated by commas allow us to read and analyse the data
automatically when we want to see whether there is any pattern
in a large volume of data.
The log holds five fields:
- Date
- Time
- Some descriptive text
- The Access error number
- The Access error message
The third parameter is particularly useful because it lets
us give the user something friendlier than the typical Access
error message. The error handler logs all five parameters but
only displays the friendly message to the user.
Generating a log
Every routine in the application has an On Error call to
the ErrorHandler sub.
Public Sub
ErrorHandler(strReason As String)
Dim
lngNumber As Long
Dim strDescription
As String
lngNumber = Err.Number
strDescription = Err.Description
On Error GoTo Err_ErrorHandler
Open
gstrHome & "\Error.Log"_
For Append Lock Write As
#1
Print #1,
"[" &
Date &
"],[" &
Time &
"],[" & _
strReason &
"],[" &
lngNumber &
"],[" & _
strDescription &
"]"
Close #1
MsgBox strReason,
vbCritical
Application.Quit acExit
End Sub
Using the log
A typical call would be:
ErrorHandler "Backup Failed"
in the On Error section of the backup routine. The user does
not need to know exactly where or why the backup has failed.
All that the user needs to know is that the backup has failed
and that they should call the support line. The rest of the
detail will be in the log file.
Extending the log
This is a simplified version of the code we use. The full log
stores the id of the user and pc and an error grade is passed
in as an extra parameter.
The lowest grade is 'Silent' for an event which is not really
an error but which needs to be logged without any message
being displayed to the user. Events such as 'Logon', 'Logoff'
and 'Rebuild database' can be significant when debugging or
when trying to tune the performance of the database.
The highest grade is 'Fatal' for an event where there can be
no recovery and where the only possible response is an
immediate shutdown.
More information on
error handling
in general.
|