Centralized Error Handler and Error Log ~ LEARN MS-ACCESS TIPS AND TRICKS

Sunday, March 11, 2012

Centralized Error Handler and Error Log

Introduction.

In an earlier article on the VBA Utility program, we explored how to scan through a VBA module—whether a standard module or a form/report class module—and automatically insert missing error-handling lines. You can find the link to that article here.

This utility program can save you considerable time that would otherwise be spent typing, copying and pasting, and modifying hundreds of lines of error-trapping code in your subroutines and functions. The main purpose of an error handler is to manage unexpected errors and, when necessary, report them to the developer so that the underlying issue can be permanently fixed. At the same time, the program should not terminate abruptly. If it is a minor issue, the user should be able to dismiss the error and continue working without interruption.

A typical project may contain hundreds of subroutines and functions across standard modules and Form/Report modules. When an error occurs, the message typically includes the error number and description. If the procedure name is included in the MsgBox() function’s title parameter, it will also appear in the message box title. However, users often overlook this important detail, which could otherwise help the developer quickly locate the exact procedure where the error occurred and resolve it efficiently.

A Common Error Handler.

A more effective approach to handling such issues is to create a centralized error handler and maintain an error log Text File on disk. Whenever an error occurs in a function or subroutine, the common error handler can be called with the necessary parameters, like the Error number, Description, procedure name, module name, and database name. The handler will both display the error message to the user and record the details in a log file.

If several Microsoft Access applications are running on a Local Area Network, all of their error log information can be saved to a single shared text file on the Server. Each log entry will include details like the date, time, module name, and database name, along with the usual error number and description, creating a consolidated and traceable error history.

A Text file image with sample error log entries is given below:

Right-click to open Large Image in New Tab/Window.

Error Message Info.

Each error log entry contains all the essential details—such as the date and time of the error, database path, module name, and procedure name—to precisely identify where the error occurred. Even if users choose not to report problems, the administrator can periodically review the log file to monitor the application’s overall health and address issues proactively.

The Trial Run.

The following sample data processing program, DataProcess(), attempts to open the input table Table_1, but the table doesn’t exist (got deleted or renamed by mistake), and the program runs into an error:

Public Function DataProcess()
Dim db As Database, rst As Recordset, x
On Error GoTo DataProcess_Error

Set db = CurrentDb
Set rst = db.OpenRecordset("Table_1", dbOpenDynaset)

Do While Not rst.EOF
 x = rst.Fields(0).Value
Loop
rst.Close

DataProcess_Exit:
Exit Function

DataProcess_Error:
BugHandler Err, Err.Description, "DataProcess()", "Module4", CurrentDb.Name
Resume DataProcess_Exit
End Function

Common Error Handler Info and Log File.

When the above program runs into an error, it calls the BugHandler() Program and passes the Module Name and Database Path as the last two parameters in addition to Error Number, Error Description, and Program name. The VBA Code of BugHandler() main program is given below:

Public Function BugHandler(ByVal erNo As Long, _
                           ByVal erDesc As String, _
                           ByVal procName As String, _
                           ByVal moduleName As String, _
                           ByVal dbName As String)
On Error GoTo BugHandler_Error
Dim logFile As String
Dim msg As String

'Error Log text file pathname, change it to the correct path
'on your Local Drive or Server Location
logFile = "c:\mdbs\bugtrack\acclog.txt"

'Open log file to add the new error log entry
Open logFile For Append As #1
  'write the log details to log file
  Print #1, Now() & vbCr
  Print #1, "Database : " & dbName & vbCr
  Print #1, "Module   : " & moduleName & vbCr
  Print #1, "Procedure: " & procName & vbCr
  Print #1, "Error No.: " & erNo & vbCr
  Print #1, "Desc.    : " & erDesc & vbCr
  Print #1, String(80, "=") & vbCr
  Close #1

msg = "Procedure Name: " & procName & vbCr & "Error : " & erNo & " : " & erDesc
  MsgBox msg, , "BugHandler()"

BugHandler_Exit:
Exit Function

BugHandler_Error:
MsgBox Err & " : " & Err.Description, , "BugHandler()"
Resume BugHandler_Exit
End Function

The Library Database.

You can save the above code in a common Library Database, where you have saved your own common library functions, so that they can be attached to your Projects.

This method will write out the details of errors from your databases into a commonplace, accessible to you all the time. When an error is reported by the User, you can directly check the details of it without asking the user to spell it out.

Technorati Tags: Databases, Error Handler

LEARN MS-ACCESS TIPS AND TRICKS

Sunday, March 11, 2012