DataWright Information Services

Consulting and Resources for Excel and Access

Creating a log table

If your database runs a regular process with multiple steps, and you want to be able to track when the process was run, you need a log to serve as an audit trail. By adding a timestamp field to the log, you can work out which steps are slowing you down and optimise them. A couple of years ago I used this log file to reduce a process from over 30minutes to less than 5 minutes.

There are two alternative versions of the code below. One uses a DAO recordset and the other uses a SQL statement, but both do the same job, so make sure that you only copy one code sample. You will need a table called tblLog, with these fields:

Field Data Type Comments
LogID AutoNumber
LogEvent Text (255)
LogProcess Text (255)
CreateDate Date/Time Default value is =Now()

Then copy either of these code samples to a module in your database:

Option 1: Using a Recordset

To use DAO, you need to set a reference to the Microsoft DAO 3.6 Library

Function WriteLog(strEvent As String, strProcess As String)
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb()
  Set rst = dbs.TableDefs("tblLog").OpenRecordset
  With rst
    !LogEvent = strEvent
    !LogProcess = strProcess
  End With
  Set rst = Nothing
  Set dbs = Nothing
End Function

Option 2: Using SQL

Function WriteLog(strEvent As String, strProcess As String)
  Dim sSQL As String

  sSQL = "INSERT INTO tblLog ( LogEvent, LogProcess ) " _
    & "VALUES ('" & strEvent & "', '" & strProcess & "')"

  DoCmd.SetWarnings False
  DoCmd.RunSQL sSQL
  DoCmd.SetWarnings True
End Function

Using the function

Whenever you want to log the start or end of a process you insert a line into your code that writes out a log record. This snippet gives an example:

  Call WriteLog(" ", " ") 'This inserts a blank line in the log
  Call WriteLog("Start", "The name of your process")
  'in here, put the process code --
  'example: DoCmd.OpenQuery "qrySomeQuery"
  Call WriteLog("End", "The name of your process")

There you go; a table with four fields and a simple function. I hope you find it useful.