Building an Audit Trail in Microsoft Access

An audit trail answers the question: "Who changed this record, and when?" In regulated industries, audit trails are a compliance requirement. In any business, they are invaluable for investigating data discrepancies, recovering from accidental changes, and understanding how data evolves over time.

Access does not have a built-in audit trail, but you can build one with VBA in a few hours. Here is the complete approach.

Designing the Audit Log Table

Create a table called tblAuditLog with these fields:

Field	Type	Description
AuditID	AutoNumber	Primary key
TableName	Short Text (100)	Which table was changed
RecordID	Long Integer	Primary key of the changed record
FieldName	Short Text (100)	Which field was changed
OldValue	Long Text	Value before the change
NewValue	Long Text	Value after the change
ChangeType	Short Text (10)	"INSERT", "UPDATE", or "DELETE"
ChangedBy	Short Text (100)	Username of the person who made the change
ChangedAt	Date/Time	When the change was made

The Core Logging Function

Create a module with a reusable logging function:

Public Sub LogChange(tableName As String, recordID As Long, fieldName As String, _
                     oldValue As Variant, newValue As Variant, changeType As String)
    On Error Resume Next  ' Never let logging crash the main operation
    
    Dim db As Database
    Set db = CurrentDb
    
    ' Only log if the value actually changed
    If changeType = "UPDATE" And Nz(oldValue, "") = Nz(newValue, "") Then Exit Sub
    
    Dim sql As String
    sql = "INSERT INTO tblAuditLog " & _
          "(TableName, RecordID, FieldName, OldValue, NewValue, ChangeType, ChangedBy, ChangedAt) " & _
          "VALUES ('" & tableName & "', " & recordID & ", '" & fieldName & "', " & _
          "'" & Replace(Nz(oldValue, ""), "'", "''") & "', " & _
          "'" & Replace(Nz(newValue, ""), "'", "''") & "', " & _
          "'" & changeType & "', '" & CurrentUser() & "', #" & Now() & "#)"
    
    db.Execute sql
    Set db = Nothing
End Sub

Tracking Updates on a Form

The key to tracking changes is capturing the old value before the change and the new value after. Use the form's BeforeUpdate event:

' In the form module:
Private oldValues As New Collection  ' Store old values before update

Private Sub Form_Current()
    ' Capture current values when navigating to a record
    Set oldValues = New Collection
    
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            On Error Resume Next
            oldValues.Add Nz(ctl.Value, ""), ctl.Name
            On Error GoTo 0
        End If
    Next ctl
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Log changes for each modified field
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            On Error Resume Next
            Dim oldVal As String
            oldVal = oldValues(ctl.Name)
            Dim newVal As String
            newVal = Nz(ctl.Value, "")
            
            If oldVal <> newVal Then
                LogChange "Customers", Me.CustomerID, ctl.Name, oldVal, newVal, "UPDATE"
            End If
            On Error GoTo 0
        End If
    Next ctl
End Sub

Tracking Inserts

Log new records in the form's AfterInsert event:

Private Sub Form_AfterInsert()
    LogChange "Customers", Me.CustomerID, "(new record)", "", Me.CustomerName, "INSERT"
End Sub

Tracking Deletes

Log deletions in the form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    LogChange "Customers", Me.CustomerID, "(deleted)", Me.CustomerName, "", "DELETE"
End Sub

A Simpler Approach: Timestamp Fields

For lighter-weight auditing, add timestamp fields directly to your data tables:

Field	Type	Description
CreatedBy	Short Text	User who created the record
CreatedAt	Date/Time	When the record was created
ModifiedBy	Short Text	User who last modified the record
ModifiedAt	Date/Time	When the record was last modified

Set these automatically in the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.CreatedBy = CurrentUser()
        Me.CreatedAt = Now()
    End If
    Me.ModifiedBy = CurrentUser()
    Me.ModifiedAt = Now()
End Sub

This approach is simpler but only tells you who last touched a record — not what changed or what the previous values were.

Viewing the Audit Log

Create a query or form to view the audit log:

SELECT AuditID, TableName, RecordID, FieldName, 
       OldValue, NewValue, ChangeType, ChangedBy, ChangedAt
FROM tblAuditLog
ORDER BY ChangedAt DESC;

Add filters for date range, user, table, and record ID to make it easy to investigate specific changes.

Archiving Old Audit Records

Audit logs grow quickly. Archive records older than a year to keep the active log manageable:

Sub ArchiveOldAuditRecords()
    Dim cutoffDate As Date
    cutoffDate = DateAdd("yyyy", -1, Date())
    
    ' Copy to archive table
    CurrentDb.Execute "INSERT INTO tblAuditLog_Archive SELECT * FROM tblAuditLog " & _
                      "WHERE ChangedAt < #" & cutoffDate & "#"
    
    ' Delete from active log
    CurrentDb.Execute "DELETE FROM tblAuditLog WHERE ChangedAt < #" & cutoffDate & "#"
    
    MsgBox "Archived audit records older than " & Format(cutoffDate, "Long Date")
End Sub

Conclusion

An audit trail is one of the most valuable additions you can make to a production Access database. The field-level change log approach described here provides complete visibility into every data change — who made it, when, and what the values were before and after. For regulated industries, this is a compliance requirement. For everyone else, it is the safety net that makes data recovery possible when something goes wrong.