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
.AddNew
!LogEvent = strEvent
!LogProcess = strProcess
.Update
.Close
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.
