VBA & Macros

Building an Audit Log in Access with VBA

Knowing who changed what and when is essential for compliance and troubleshooting. Here is how to build a reliable audit trail in Access using VBA form events and a log table.

A
Anthony Passeri, MBA
••7 min read
Building an Audit Log in Access with VBA

"Who changed that record?" is a question that comes up in almost every mature Access database. Without an audit log, the answer is always "we do not know." With one, you can trace every change back to a specific user and timestamp.

Audit logging is also a compliance requirement in many industries — healthcare, finance, legal — where you need to demonstrate that data has not been altered without authorization.

Here is how to build a complete audit trail in Access using VBA.

The Audit Log Table

Start with a table to store the audit records. Create tblAuditLog with these fields:

FieldTypeDescription
AuditIDAutoNumberPrimary key
AuditDateDate/TimeWhen the change occurred
UserNameShort Text (100)Who made the change
TableNameShort Text (100)Which table was affected
RecordIDLong IntegerPrimary key of the changed record
FieldNameShort Text (100)Which field was changed
OldValueLong TextValue before the change
NewValueLong TextValue after the change
ActionTypeShort Text (20)INSERT, UPDATE, or DELETE

Using Long Text for OldValue and NewValue handles any field type — numbers, dates, and text all convert to strings for storage.

The Core Logging Function

Add this function to a standard module (not a form module — it needs to be callable from anywhere):

Public Sub WriteAuditLog( _
    strTableName As String, _
    lngRecordID As Long, _
    strFieldName As String, _
    varOldValue As Variant, _
    varNewValue As Variant, _
    strActionType As String)

    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    ' Do not log if values are identical
    If strActionType = "UPDATE" Then
        If Nz(varOldValue, "") = Nz(varNewValue, "") Then Exit Sub
    End If

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblAuditLog", dbOpenDynaset)

    rs.AddNew
    rs!AuditDate  = Now()
    rs!UserName   = Environ("USERNAME")   ' Windows login name
    rs!TableName  = strTableName
    rs!RecordID   = lngRecordID
    rs!FieldName  = strFieldName
    rs!OldValue   = Nz(varOldValue, "")
    rs!NewValue   = Nz(varNewValue, "")
    rs!ActionType = strActionType
    rs.Update

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    ' Silently fail — do not let audit logging break the main operation
    On Error Resume Next
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

The Environ("USERNAME") call gets the Windows login name of the current user. If your database has its own user authentication, replace this with your user identification method.

The error handler silently fails — audit logging should never prevent the main operation from completing. Log the error separately if you need to track logging failures.

Logging Updates: Capturing Before and After Values

The most common audit scenario is tracking field changes. Add this to the form's BeforeUpdate event to capture old values before they are overwritten:

' In the form module — store old values before update
Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Store old values in form-level variables
    If Not Me.NewRecord Then
        m_lngOldCustomerID = Me.CustomerID
        m_strOldFirstName  = Nz(Me.txtFirstName.OldValue, "")
        m_strOldLastName   = Nz(Me.txtLastName.OldValue, "")
        m_strOldEmail      = Nz(Me.txtEmail.OldValue, "")
        m_strOldStatus     = Nz(Me.cboStatus.OldValue, "")
    End If
End Sub

Declare the module-level variables at the top of the form module:

Private m_lngOldCustomerID As Long
Private m_strOldFirstName  As String
Private m_strOldLastName   As String
Private m_strOldEmail      As String
Private m_strOldStatus     As String

Then in the AfterUpdate event, compare old and new values and log any that changed:

Private Sub Form_AfterUpdate()
    Dim lngID As Long
    lngID = Me.CustomerID

    If Me.NewRecord Then
        ' Log the insert
        WriteAuditLog "tblCustomers", lngID, "(new record)", "", "", "INSERT"
    Else
        ' Log each changed field
        If m_strOldFirstName <> Nz(Me.txtFirstName, "") Then
            WriteAuditLog "tblCustomers", lngID, "FirstName", _
                m_strOldFirstName, Nz(Me.txtFirstName, ""), "UPDATE"
        End If

        If m_strOldLastName <> Nz(Me.txtLastName, "") Then
            WriteAuditLog "tblCustomers", lngID, "LastName", _
                m_strOldLastName, Nz(Me.txtLastName, ""), "UPDATE"
        End If

        If m_strOldEmail <> Nz(Me.txtEmail, "") Then
            WriteAuditLog "tblCustomers", lngID, "Email", _
                m_strOldEmail, Nz(Me.txtEmail, ""), "UPDATE"
        End If

        If m_strOldStatus <> Nz(Me.cboStatus, "") Then
            WriteAuditLog "tblCustomers", lngID, "Status", _
                m_strOldStatus, Nz(Me.cboStatus, ""), "UPDATE"
        End If
    End If
End Sub

Logging Deletes

Deletes are the most important changes to audit — once a record is gone, you cannot recover the data without a log. Add this to the form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    ' Log the delete before it happens
    Dim lngID As Long
    lngID = Me.CustomerID

    WriteAuditLog "tblCustomers", lngID, "FirstName", Nz(Me.txtFirstName, ""), "", "DELETE"
    WriteAuditLog "tblCustomers", lngID, "LastName",  Nz(Me.txtLastName, ""),  "", "DELETE"
    WriteAuditLog "tblCustomers", lngID, "Email",     Nz(Me.txtEmail, ""),     "", "DELETE"
    WriteAuditLog "tblCustomers", lngID, "Status",    Nz(Me.cboStatus, ""),    "", "DELETE"
End Sub

Alternatively, log a single "record deleted" entry with the record ID and a summary of key fields in the OldValue column:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Dim strSummary As String
    strSummary = Me.txtFirstName & " " & Me.txtLastName & " (" & Me.txtEmail & ")"

    WriteAuditLog "tblCustomers", Me.CustomerID, "(record deleted)", strSummary, "", "DELETE"
End Sub

Viewing the Audit Log

Create a form or report to view audit records. A simple query for viewing changes to a specific record:

SELECT
    AuditDate,
    UserName,
    FieldName,
    OldValue,
    NewValue,
    ActionType
FROM tblAuditLog
WHERE TableName = 'tblCustomers'
  AND RecordID = [Enter Customer ID]
ORDER BY AuditDate DESC;

For a more useful view, add a form with a combo box to select a customer and a subform showing their audit history. This gives support staff and managers a clear view of what changed and when.

Keeping the Log Table Manageable

Audit logs grow quickly in active databases. A table with 50 users making 20 changes per day generates 1,000 rows per day — 365,000 rows per year. After a few years, the log table can become a performance concern.

Strategies for managing log size:

Archive old records. Move records older than 12 or 24 months to an archive table or export them to a separate database. Keep the active log table small.

Be selective about what you log. Not every field change needs to be audited. Focus on fields that matter for compliance or troubleshooting: status changes, financial values, key identifiers. Skip cosmetic fields like notes or formatting preferences.

Index the log table. Add indexes on TableName, RecordID, and AuditDate to keep queries fast as the table grows.

The Migration Perspective

Access audit logging through VBA form events works, but it has a fundamental limitation: it only captures changes made through your forms. Direct table edits, bulk SQL updates, and changes made by other applications bypass the form events entirely.

SQL Server solves this with triggers — code that runs at the database level on every INSERT, UPDATE, and DELETE, regardless of how the change was made. Dataverse has built-in auditing that captures all changes automatically with no custom code.

If audit completeness is important for your use case, it is one of the strongest arguments for migrating to a platform with server-side change tracking. The Access VBA approach is a good interim solution — but it is not a substitute for database-level auditing when the stakes are high.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Need an audit log in your Access database? Schedule a free call and get expert help implementing a reliable audit trail.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#VBA#audit log#compliance#Access development#data tracking

Keep reading

More on VBA & Macros