An Access Error Log

Maintenance and debugging VBA code in an Access database 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:

  1. Date
  2. Time
  3. Some descriptive text
  4. The Access error number
  5. 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 the friendly message is all that the user sees.

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        ' Error number
Dim strDescription As String  'Error description

'-- Save these values before the On Error clears
'-- them

lngNumber = Err.Number
strDescription = Err.Description

On Error GoTo Err_ErrorHandler

'-- gstrHome is the name of a common network drive
'-- available to all users

Open gstrHome & "\Error.Log"_
   For Append Lock Write As #1

'-- Write some basic info then close the file
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.