You are here: MS Access Products > UI Builder > Product Support > Event Logging

UI BuilderTM for Microsoft® AccessTM

   Optimizing Database Performance Using Event Logging

The Problem

Optimizing the response times and processing speed of a database is a common challenge when you have multiple complex queries, or where you have deployed a back-end database on a file server.  For example, if you've deployed your front-end database to several users, and only one complains of poor performance when they execute a pre-defined process you created, it can be time consuming and inconvenient to sit at the user's computer, add debug.print statements, and watch for bottlenecks in processing.
  

Solution

Use UI Builder's Event Logging capability to capture key events during your application's processing.  You can save the log information to a central file server (instead of sitting at the user's computer), and you can add the events to your VBA code as debug events, which means that they will only be written to the log when you've turned on debugging.  At a later time, from the comfort of your own PC, you can see which steps in your application's processing are causing performance issues and take the necessary steps to optimize performance.  For example, by adding indexes to tables to make queries run faster, optimizing your VB functions, or determining the user's PC needs to be upgraded to handle the processing.

Example Implementation

Place the fLogEvent function call at each key point in your code where you want to evaluate potential bottlenecks.  For example, before and after every major time you initiate a query, macro, or import/export.

Function ProcessSomething()

fLogEvent "Starting processing - running query 'some query'", auiDebug, False, "ProcessSomething", auiInformation

DoCmd.OpenQuery "some query"

fLogEvent "Done running 'some query', running query 'another query'", auiDebug, False, "ProcessSomething", auiInformation

DoCmd.OpenQuery "another query"

fLogEvent "Done running 'another query', running macro 'macro1'", auiDebug, False, "ProcessSomething", auiInformation

DoCmd.RunMacro "macro1"

fLogEvent "Done running macro 'macro1', running 'macro2'", auiDebug, False, "ProcessSomething", auiInformation

DoCmd.RunMacro "macro2"

fLogEvent "Done running macro 'macro2', running 'macro3'", auiDebug, False, "ProcessSomething", auiInformation

DoCmd.RunMacro "macro3"

fLogEvent "Done running macro 'macro3', function complete.", auiDebug, False, "ProcessSomething", auiInformation

End Function

 
In this case, each log event captured will display the text specified, like "Starting process..." and the timestamp.  You can then easily do a visual scan through a given process set and see which particular stages of processing are taking the longest.  Best of all, you can turn on the Debug logging from the back-end database, and view the logs without having to be at the user's PC.


MS Access event logging


Once you have activated event logging and are using it in your own code, you can open the event log viewer as shown above, to review major events in your database, filtering by type of event, user, machine, and source.