3 Methods of Logging in VBA

Mike Wolfe -

In my view, there are three basic ways to log information in an Access application:

  1. Temporary Logging (e.g., message boxes and the immediate window)
  2. Permanent Logging (e.g., writing to a text file or database table)
  3. Logging Framework (an event-based system supporting all of the above)

Temporary Logging

Temporary logging is by far the most common form of logging in VBA.

Throw in a few Debug.Print statements and you can get a quick and dirty view into how your code is executing without having to step through it line by line.  If you don't have access to the immediate window (e.g., while troubleshooting an issue that works on your computer but not the user's), then you sprinkle in a few MsgBox calls, et voila: instant logging.

The biggest downside to temporary logging is that it's, well, temporary.

Some (or even most) of the time, that's sufficient for our purposes.  But occasionally, it's helpful to keep a more permanent record of what's going on.  This is especially true when trying to catch an intermittent error or race condition.

Permanent Logging

Permanent logging is useful in several situations:

  • Maintaining a list of errors displayed to the users
  • Gathering data to understand the root causes of intermittent errors
  • Recording usage data for later aggregation to identify commonly used forms and reports (e.g., telemetry)

Here are three ways to permanently log data:

  1. Appending to a text file (see my LogToTempFile() and FileAppend() functions)
  2. Saving to a database table
  3. Generating cases in a web-based bug database (e.g., FogBugz, Jira, or GitHub)

If you use option 2 or 3 above, it's often wise to include text file logging as a fallback in case you run into errors saving to the database or communicating with an online service.

The biggest concern with any sort of permanent logging is how much storage space it consumes.  An overly verbose log file can easily grow out of control if you're not careful.  If you want to automate error logging to an online service, make sure it has a way to automatically aggregate errors by assigning them to existing cases with matching descriptions, otherwise you risk flooding your bug database with a single recurring error.

Logging Framework

A logging framework consists of three basic parts:

  1. A logging class that raises events for various logging levels (e.g., Debug, Info, Error)
  2. One or more "loggers" that handle raised events (e.g., FileLogger, MsgBoxLogger, DatabaseLogger)
  3. One or more instances of the logging class, each with one or more loggers defined with corresponding minimum logging levels

Let's consider a relatively simple example:

  • A logging class with three logging levels: Debug, Info, and Error
  • A single, global instance of the logging class represented by a function named, Log()
  • A "text file logger" that appends the text of every message (Debug/Info/Error) to a text file
  • A "database logger" that saves the text of each informational message (Info/Error) to a database table
  • A "message box logger" that displays the text of each error message (Error) to the user via a MsgBox() call

Let's imagine the above system had the following test procedure:

Sub TestLogging()
    Log.Debug "Debug message"
    Log.Info "Info message"
    Log.Error "Error message"
End Sub

Here's what would happen if you were to execute the TestLogging() procedure:

  • You would see a single message box that said, "Error message"
  • You would have a text file with the following three lines in it:
    Debug message
    Info message
    Error message
  • You would have a database table with the following two records in it:
    Info message
    Error message

The biggest benefit to a logging framework is flexibility.

It allows you to blend the benefits of both the permanent and temporary logging solutions.  There is no need to remove logging code.  You simply leave it in place and adjust the logging level of the individual loggers (or disable them entirely).  When it comes time troubleshoot a new issue, you can temporarily increase the logging level of one or more loggers.

I'll have more on logging frameworks–including sample code to implement your own VBA logging framework–in a future article.

Cover image generated by Ideogram.