Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
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.
A typical entry in our log file looks like this:
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:
- 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 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.
Dim lngNumber As Long ' Error number
Dim strDescription As String 'Error description
'-- Save these values before the On Error clears
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 & "]"
MsgBox strReason, vbCritical
Using the log
A typical call would be:
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.