Simple Audit Log

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Published:
Edited by: Andrew Leniart
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.

In response to a question posted here recently, I started thinking about how to implement an audit log which would allow the user to log inserts, edits, and deletions from tables within Access.  Prior to the advent of Data Macros, this process had to be done via code, and had some pitfalls, as documented by the Access MVP Allen Browne on his website.  But in the code on Allen's website, he writes an entire record to his audit table every time even a single field was changed;  this means that you need a separate audit log table for each table you need to audit.


My goal was to create a relatively simple process which would meet the needs of the vast majority of people and which could be implemented quite simply.  To do this, I came up with a single audit log table which contains the following fields.

The fields in this log table are defined as:

  • TableName: (text, 50)  This allows you to write inserts, edits or deletes for any table to the same log file
  • Action: (text, 10)  Insert, Edit, or Delete
  • ActionBy: (text, 30) the Windows UserID of the person making the edits
  • ActionDT: date/time, defaults to Now()
  • RecordID: this field is used to store the PK value of the record being inserted, edited, or deleted.  This requires that each table being audited contain a single, long integer (autonumber) field which is the PK for the table.
  • FieldName: (text, 50) Name of the field that is inserted, edited, or deleted.  During an Insert or Delete operation, every field but the PK field is written as a separate record into the Audit Log.  During an Edit operation, only the fields whose Value and OldValues differ are written to the log.
  • FieldValue: (text, 255) I elected to use a text field of length (255) to store all of the field values rather than have a separate field for each potential datatype.  This will accomodate the vast majority of fields in a database (except attachment, multi-value, and long binary (OLE)).


In order to implement this Audit log, you only need to add a couple of lines of code to the form(s) used to edit your tables and you will be on your way.  When inserting or editing a record on your form, you would use the Form_BeforeUpdate and Form_AfterUpdate events, when deleting a record, you would need to add code to the Form_Delete and Form_AfterDeleteConfirm events.  


As Allen Browne highlighted on his site, you cannot simply write the records to your audit log in the BeforeUpdate and Delete events because each of these actions can be cancelled by the user after the data is written to the log.  To resolve this I first write the information to tbl_Audit_Log_Temp, and then in the AfterUpdate and AfterDeleteConfirm events, if the Update or Delete operations were not cancelled, I copy the data from tbl_Audit_Log_Temp to tbl_Audit_Log and then delete the data from the temporary table.


The only line you will need to add to your Form_BeforeUpdate event is highlighted in the code segment below.  This should be the last line of code in the event procedure and should only be executed if all of the error checks and field validation checks have passed.

Private Sub Form_BeforeUpdate(Cancel as Integer)

    on Error goto ProcError

    'Enter other error checking code here

    'do not allow code execution to reach this line if you are canceling the Update operation
    Call AuditLog(Me, "tbl_Audit_Test", IIf(Me.NewRecord, "Insert", "Edit"), "ID")

ProcExit:
    Exit Sub
ProcError:
    'process errors here

    Cancel = true
End Sub

To log record deletions, you need only add a single line of code to the Form_Delete event, as indicated below. 

Private Sub Form_Delete(Cancel As Integer)

    Call AuditLog(Me, "tbl_Audit_Test", "Delete", "ID")
   
End Sub

Then, to ensure that edits or deletion operations are actually logged, you need to call the AuditLogConfirm function with the following code.  The value of the Status argument in the AfterDelConfirm event will be either 0 (deletion confirmed) or 2 (deletion cancelled), so I wrote this procedure to accept a zero as the Confirmed value.

Private Sub Form_AfterUpdate()

    AuditLogConfirm (0)
   
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

    Call AuditLogConfirm(Status)
   
End Sub

The real guts of this process is the AuditLog subroutine, which requires parameters for the name of the table being edited, the action being performed (Insert, Edit, Delete), and the name of the Primary Key (PK) field used in the table being audited (the pimary key must be a single, numeric value, preferably an autonumber).

Public Sub AuditLog(frm As Form, TableName As String, Action As String, PKFieldName As String)

    Dim dtAuditAt As Date
    Dim strAuditedBy As String
    Dim pkValue As Long
    Dim bRecord As Boolean
   
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
   
    dtAuditAt = Now()
    strAuditBy = fOSUserName()
   
    Set db = CurrentDb
    strSQL = "SELECT * FROM " & IIf(Action = "DELETE", "tbl_Audit_Log_Temp", "tbl_Audit_Log") _
           & " WHERE 0 = 1"
    Set rs = db.OpenRecordset(strSQL,, dbFailOnError)
   
    dtAuditAt = Now()
    strAuditedBy = fOSUserName()
    pkValue = frm(PKFieldName).Value
   
    For Each fld In frm.RecordsetClone.Fields
   
        If (fld.Name = PKFieldName) Then
            bRecord = False
        ElseIf (Action = "Insert") Or (Action = "DELETE") Then
            bRecord = True
        ElseIf (Nz(frm(fld.Name).OldValue, "") <> Nz(frm(fld.Name).Value, "")) Then
            bRecord = True
        Else
            bRecord = False
        End If

        'If you are auditing a table with memo or OLE fields, then do not attempt
        'to add those values to the FieldValue field in the Audit Log
        If (fld.Type = dbMemo) Or (fld.Type = dbLongBinary) Then
            bRecord = False
        End If       

        If bRecord Then
           
            rs.AddNew
            rs!Action = Action
            rs!TableName = TableName
            rs!ActionBy = strAuditedBy
            rs!ActionDT = dtAuditAt
            rs!RecordID = pkValue
            rs!FieldName = fld.Name
            rs!FieldValue = CStr(Nz(frm(fld.Name).Value, ""))
            rs.Update
       
        End If
       
    Next
   
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
End Sub

The code initially opens the Audit Log Temp table, and then loops through the list of fields bound to the form via the forms RecordSource property.  The code determines which fields to write to the audit log (If the action is an Insert or Delete operation all fields get logged;  if it is an Edit operation only those fields whose values have changed during the current operation are written to the log) and writes those values, along with the table name, ActionBy, ActionDT, and RecordID values.


The AuditLogConfirm procedure simply copies the records from the temporary table to the permanent Audit Log, if the value passed into the procedure is a zero (0).  Otherwise, it skips that section of code and deletes the records in the temporary table.

Public Sub AuditLogConfirm(Confirmed As Integer)

    Dim strSQL As String
    Dim db As DAO.Database
   
    Set db = CurrentDb
    If Confirmed = 0 Then
   
        strSQL = "INSERT INTO tbl_Audit_Log (TableName, Action, ActionBy, ActionDT, RecordID, " _
                                          & "FieldName, FieldValue) " _
               & "SELECT TableName, Action, ActionBy, ActionDT, RecordID, FieldName, FieldValue " _
               & "FROM tbl_Audit_Log_Temp"
        db.Execute strSQL, dbFailOnError
       
    End If
   
    strSQL = "DELETE FROM tbl_Audit_Log_Temp"
    db.Execute strSQL, dbFailOnError
   
    Set db = Nothing
   
End Sub

What I think is neat about this method of storing the data is that we can now write queries against the audit log to view the changes we have made to each record over time.  You can do this with the method Allen uses as well, but it is much more difficult to determine what values have changed.  With this method, and the use of a cross-tab query, only the values that have changed are displayed on each row, and the value displayed is the new value.  The sample database contains several records with a series of field changes.  You can see those changes in the results of qry_Audit_Log_XTAB:


Big, bold, NOTE:  This technique will not work if you are running a DELETE, INSERT, or UPDATE query.  In order to implement a procedure similar to this in a query, I believe you would have to develop this functionality in a data macro, would have to check for each field in the table independently (no looping through field names) and have rows in the data macro for each of the fields being written to the table.  This would be a lot of work!!!


Hope you find this article and this technique useful.


Audit Log.accdb


Dale


3
2,419 Views
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT

Comments (17)

Sarina MossaQuality Control

Commented:
Access 2013, Windows 10, I have compiled and I have checked references.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
What does the data look like in my application, after you made the changes I recommended?

run that crosstab query and send me a screen shot.
Sarina MossaQuality Control

Commented:
I had to change field to fields in order for it to work but it works perfectly. Its my database that still doesnt work
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Sarina,

I'm slammed today, but might be able to do a team viewer session with you some day next week so that I can look at your screen, and possibly help you figure it out.

Dale
Sarina MossaQuality Control

Commented:
Thank you! Mine is just not recognizing that one part of the code that compares old and new value that is why it wont respond unless I put edit.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.