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.
"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:
| Field | Type | Description |
|---|---|---|
| AuditID | AutoNumber | Primary key |
| AuditDate | Date/Time | When the change occurred |
| UserName | Short Text (100) | Who made the change |
| TableName | Short Text (100) | Which table was affected |
| 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 |
| ActionType | Short 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.
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.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on VBA & Macros
VBA Debugging Techniques Every Access Developer Needs
The VBA debugger is one of the most powerful tools in Access — and one of the most underused. Here is how to use breakpoints, the Immediate window, and watch expressions effectively.
Automating Access Reports with VBA
Stop manually opening and printing reports. VBA lets you automate report generation, apply filters on the fly, export to PDF, and email output — all without user intervention.
VBA Class Modules: Write Cleaner Access Code
Class modules bring object-oriented structure to VBA. Learn how to use them in Access to reduce repetition, improve maintainability, and write code that scales.