Tracking who changed what data is essential for security, compliance (HIPAA, SOX, financial regulations), and accountability. This tutorial shows you how to implement audit logging in both Microsoft Access and Excel, creating tamper-proof records of all data changes.
An effective audit log should capture:
User identification (Windows login, user ID, or credentials).
Which record, field, or cell was changed.
Timestamp of the change (date and time).
The previous value before the change.
The new value after the change.
Insert, Update, or Delete operation.
Create a table to store audit log entries:
Create → Table Design.
Create these fields: AuditID (AutoNumber, Primary Key), TableName (Text, 50), RecordID (Text, 50), FieldName (Text, 50), OldValue (Memo), NewValue (Memo), UserName (Text, 50), ChangeDate (Date/Time), ActionType (Text, 10).
Set ChangeDate default to Now() to automatically timestamp entries.
Save as 'tblAuditLog' or similar name.
Create a VBA function to write audit log entries. Press Alt+F11 to open VBA editor, insert a new module, and add this code:
Function WriteAuditLog(TableName As String, RecordID As String, FieldName As String, OldValue As Variant, NewValue As Variant, ActionType As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAuditLog", dbOpenDynaset)
rs.AddNew
rs!TableName = TableName
rs!RecordID = RecordID
rs!FieldName = FieldName
rs!OldValue = OldValue
rs!NewValue = NewValue
rs!UserName = Environ("USERNAME")
rs!ChangeDate = Now()
rs!ActionType = ActionType
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Add audit logging to form events. Open your form in Design View, open Properties, and add code to events:
Add code to log field changes before record is saved.
Log new record creation.
Log record deletion.
Example BeforeUpdate code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If ctl.Value <> ctl.OldValue Then
WriteAuditLog Me.RecordSource, Me!ID, ctl.Name, ctl.OldValue, ctl.Value, "UPDATE"
End If
End If
Next ctl
End Sub
In Navigation Options, hide tblAuditLog from navigation pane.
Use workgroup security to grant read-only access to audit table.
Password-protect VBA project to prevent users from disabling audit logging.
Insert → New Sheet, name it 'AuditLog'.
Row 1: Timestamp, User, Sheet, Cell, OldValue, NewValue, Action.
Right-click sheet tab → Hide to prevent users from modifying it.
Review → Protect Sheet (with password) to lock the audit log.
Press Alt+F11, insert new module, add this function:
Sub WriteExcelAuditLog(SheetName As String, CellAddress As String, OldValue As Variant, NewValue As Variant)
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("AuditLog")
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
ws.Cells(LastRow, 1) = Now()
ws.Cells(LastRow, 2) = Environ("USERNAME")
ws.Cells(LastRow, 3) = SheetName
ws.Cells(LastRow, 4) = CellAddress
ws.Cells(LastRow, 5) = OldValue
ws.Cells(LastRow, 6) = NewValue
ws.Cells(LastRow, 7) = "UPDATE"
End Sub
Add code to Worksheet_Change event to automatically log changes. Right-click worksheet tab → View Code, add:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant
Application.EnableEvents = False
OldValue = Target.Value
Application.Undo
OldValue = Target.Value
Application.Undo
WriteExcelAuditLog Me.Name, Target.Address, OldValue, Target.Value
Application.EnableEvents = True
End Sub
Tools → VBAProject Properties → Protection → Lock project for viewing (set password).
Review → Protect Workbook → Structure (prevents sheet deletion/renaming).
Review → Protect Sheet for each data worksheet (allow editing but protect structure).
Add BeforeDelete event to form:
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
WriteAuditLog Me.RecordSource, rs!ID, "RECORD", "", "", "DELETE"
rs.Close
End Sub
Modify Worksheet_Change to detect formula changes:
If Target.HasFormula Then
WriteExcelAuditLog Me.Name, Target.Address, Target.Formula, Target.Formula, "FORMULA"
End If
For better user identification, create a login form in Access:
Form with username/password fields.
Store logged-in user in a global variable or temp table.
Use session variable instead of Environ('USERNAME') for more accurate tracking.
Create → Query Design.
Add tblAuditLog table to query.
Add criteria to filter by user, date range, table name, or action type.
Create report based on query for printable audit trail.
Insert → PivotTable from AuditLog data.
Add User, Sheet, Date as filters for easy filtering.
Show count of changes by user, by date, or by sheet.
Visualize audit data with charts showing change trends.
Store audit log in separate Access database with restricted access.
Use Access database encryption or Windows encryption for audit log files.
Backup audit logs regularly to prevent data loss.
Archive old audit entries to separate database to maintain performance.
Ensure audit logs meet HIPAA requirements for healthcare data access tracking.
Financial data changes must be tracked with user identification and timestamps.
Establish how long audit logs must be retained based on regulations.
Limit who can view audit logs to authorized personnel only.
Problem: Changes aren't being logged.
Solution: Check that VBA code is enabled, verify event procedures are properly named, ensure audit table exists and is accessible, test WriteAuditLog function independently.
Problem: Database or Excel becomes slow with audit logging enabled.
Solution: Optimize audit table with indexes on UserName and ChangeDate, archive old audit entries, use batch logging for multiple changes, consider asynchronous logging.
Problem: Users find ways to disable or bypass audit logging.
Solution: Password-protect VBA code, hide audit table from navigation, use workgroup security, implement server-side logging if using SQL Server backend.
For advanced topics, explore database encryption, workgroup security in Access, Excel workbook protection strategies, and compliance reporting automation. Understanding security principles and regulatory requirements will help you design more robust audit logging systems.
We audit and harden Excel/Access tools to enterprise standards. We implement audit logging, user authentication, data encryption, and compliance reporting. Whether you need HIPAA compliance, financial regulations, or general security hardening, we can make your database enterprise-ready.
Get Your Security Audit →