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:

  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 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        ' 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.

Access Tips

FoxPro Tips

General Tips


Related Items

Custom Toolbars

Your Access database will look more impressive if you add custom toolbars.

Read More

Frequent, automatic backups

How to create backups automatically in FoxPro or Access using the Windows Scripting Host

Read More

American date formats used by Access SQL

Microsoft Access uses an American date format in SQL commands

Read More

Backing up an Access Database

How to backup an Access database using VBA and DAO

Read More

Creating a datestamped filename in Access

How to create a filename from a datestamp

Read More