Module: ErrorHandlerAccess in Category Error Handling : VBA from Total Visual SourceBook

Global error handler for Microsoft Access VBA. Added to every procedure, these routines will trap, document, email, and gracefully exit your application when unexpected errors occur.

A global error handler will help you improve the quality of your application by providing developers with the information necessary to diagnose problems and minimize the need for users to report crashes. The primary procedures for this module are CallStackAdd, CallStackRemove, and GlobalErrorHandler.

For more information on error handling, read our technical paper on Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and VB6. See the declarations section for how to set the Total Visual SourceBook error handling options with this module.

If line numbers are added to your VBA code, the error handler will document the line number where the crash occurs. Use the Code Delivery feature of Total Visual CodeTools to add line numbers to your code.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modErrorHandlerAccess module.
ErrorHandlerInitialize Procedure Initialize error handling routines by resetting the procedure call stack. Call this routine at the start of your program.
ErrorHandlerEmail Procedure Set email settings if you want errors to be emailed. If these are not set, email is not sent.
CreateErrorTable Procedure Create a table in the current database with the structure to save error logs. This presumes the database is not an ADP. This procedure should be deleted when used in an ADP which doesn't support DAO. This should be called during development so your database already contains this table. Do not create the table each time you initialize the error handler. In a split database environment, this table should reside in the backend database and linked by the front-end database so that the errors encountered by all users are saved in one table. For more information, visit Microsoft Access Split Database Architecture to Support Multiuser Environments, Improve Performance and Simplify Maintainability.
CallStackAdd Procedure Adds the supplied procedure name to error handler's procedure call stack. This allows the error handler to report the series of procedures called when a crash occurs which can significantly simplify fixing the problem. Call this method at the beginning of a procedure. It must be balanced by a call to the CallStackRemove method which is called when the procedure exits normally.
CallStackRemove Procedure Move the current pointer to the previous item on the procedure call stack. The existing procedure name remains on the stack so you can see what was previously called.
GetArraySize Procedure Get the size of an array and trap for situations where the array is not defined.
GlobalErrorHandler Procedure Global error handler. This procedure is called when an unexpected error occurs. Based on the settings when ErrorHandlerInitialize was called, the error handler will generate an error log file with options to email and save it to a table.
CreateErrorLog Procedure Generate the error log text.
GetAccessServicePack Procedure Get the actual Access SP version name from the build number.
GetProcedureCallStack Procedure Create a list of procedure calls with the current procedure highlighted.
GetLibraryReferences Procedure Create a list of library references.
GetNextErrorFileName Procedure Determine the file name to store the error log with support for the option to overwrite or create a new file name.
FileExists Procedure Determine if a file name exists (returns TRUE if the file is a folder which exists).
DeleteFile Procedure Delete the named file, handling errors if the file does not exist.
CreateTextFile Procedure Create (save) a file containing the text. Deletes the file if it exists.
WindowsVersionString Procedure Get the Windows version as a string with major and minor build plus 32 or 64 bitness (e.g. "Windows 8 v.6.2, 64-bit" or "Windows 7 v.6.1, 32-bit").
ShowFile Procedure Open the error file (assumes a default program will open it based on its extension).
' Example of using the module modErrorHandlerAccess for a global error handler of your Microsoft Access VBA applications
' To use this example, create a new module and paste this code into it.
' Then run the procedure by putting the cursor in the procedure and pressing:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

Public Sub Example_modErrorHandlerAccess()
  ' Comments: Examples of how this error handler can be applied to each procedure to catch unexpected errors

  ' Table to store the error log.
  Const cstrErrorTable As String = "tblErrorLog"

  ' To save error logs into a table, the table needs to be created in advance. It should not be created every time your database runs.
  ' In a split database architecture, the table should be stored in the backend database and linked by the front-end applications.
  ' For more information on split database architecture, visit:
  ' Run this procedure to create the table:
  ' CreateErrorTable cstrErrorTable

  ' Initialize the error handler.  This should be done when your application starts.
  ' Specify the name of your application, any extra information you'd like added to the error log,
  ' the name of the error log text file to create, whether you want it to be overwritten, a form to appear, and a table to store the data
  Call ErrorHandlerInitialize("My Database Name", "", "ErrorLog.txt", True, True, "", cstrErrorTable)

  ' Set email options so the error log is emailed to you. Replace the email address with your own. You can also add CC, BCC and specify the subject.
  'Call ErrorHandlerEmail("")


End Sub

Private Sub MySampleCode()
  ' Comments: Standard error handling style used for all your procedures

  ' This puts the name of the procedure "MySampleCode" in the error handler's procedure call stack
  CallStackAdd "MySampleCode"

  ' This enables the error handler if gcfHandleErrors is True.
  ' Set it to False during development and debugging so that the error handler isn't triggered and crashes stop at the offending line
  If gcfHandleErrors Then On Error GoTo PROC_ERR

  ' Add your code here. In this example, we simply call the next procedure to build our chain of procedure calls to populate the call stack.
  ' The critical requirement of the error handler is to always leave the procedure at the bottom where CallStackRemove in invoked to track we've left this procedure.
  ' Do not use "Exit Sub" or "Exit Function" in the middle of your code.


  Exit Sub

End Sub

Private Sub FirstCall()
  ' Comments: Example of the standard error handling structure without the comments of the previous procedure

  CallStackAdd "FirstCall"
  If gcfHandleErrors Then On Error GoTo PROC_ERR

  Call SecondCall

  Exit Sub

End Sub

Private Sub SecondCall()
  ' Comments: Example of a procedure that triggers the error handler. Make sure gcfHandleErrors = True to see the error handler work

  CallStackAdd "SecondCall"
  If gcfHandleErrors Then On Error GoTo PROC_ERR

  ' Simulate an error, which jumps to the PROC_ERR section
  MsgBox (1 / 0)

  Exit Sub

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

View all FMS products for Microsoft Access All Our Microsoft Access Products


Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info




Free Product Catalog from FMS