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.
Understanding Audit Logging Requirements
An effective audit log should capture:
Who
User identification (Windows login, user ID, or credentials).
What
Which record, field, or cell was changed.
When
Timestamp of the change (date and time).
Old Value
The previous value before the change.
New Value
The new value after the change.
Action Type
Insert, Update, or Delete operation.
Part 1: Implementing Audit Logging in Access
Step 1: Create Audit Table
Create a table to store audit log entries:
Create New Table
Create → Table Design.
Add Fields
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 Defaults
Set ChangeDate default to Now() to automatically timestamp entries.
Save Table
Save as 'tblAuditLog' or similar name.
Step 2: Create Audit Function
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
Step 3: Add Audit Code to Forms
Add audit logging to form events. Open your form in Design View, open Properties, and add code to events:
BeforeUpdate Event
Add code to log field changes before record is saved.
BeforeInsert Event
Log new record creation.
BeforeDelete Event
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
Step 4: Protect Audit Table
Hide from Users
In Navigation Options, hide tblAuditLog from navigation pane.
Set Permissions
Use workgroup security to grant read-only access to audit table.
Protect VBA Code
Password-protect VBA project to prevent users from disabling audit logging.
Part 2: Implementing Audit Logging in Excel
Step 1: Create Audit Log Worksheet
Add New Sheet
Insert → New Sheet, name it 'AuditLog'.
Create Headers
Row 1: Timestamp, User, Sheet, Cell, OldValue, NewValue, Action.
Hide Sheet
Right-click sheet tab → Hide to prevent users from modifying it.
Protect Sheet
Review → Protect Sheet (with password) to lock the audit log.
Step 2: Create VBA Audit Function
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
Step 3: Add Worksheet Change Event
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
Step 4: Protect Workbook and VBA
Protect VBA Project
Tools → VBAProject Properties → Protection → Lock project for viewing (set password).
Protect Workbook Structure
Review → Protect Workbook → Structure (prevents sheet deletion/renaming).
Protect Worksheets
Review → Protect Sheet for each data worksheet (allow editing but protect structure).
Part 3: Advanced Audit Features
Track Deletions in Access
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
Track Formula Changes in Excel
Modify Worksheet_Change to detect formula changes:
If Target.HasFormula Then
WriteExcelAuditLog Me.Name, Target.Address, Target.Formula, Target.Formula, "FORMULA"
End If
User Authentication Enhancement
For better user identification, create a login form in Access:
Create Login Form
Form with username/password fields.
Store User Session
Store logged-in user in a global variable or temp table.
Update Audit Function
Use session variable instead of Environ('USERNAME') for more accurate tracking.
Part 4: Querying and Reporting Audit Logs
Access: Create Audit Report Query
Create Query
Create → Query Design.
Add tblAuditLog
Add tblAuditLog table to query.
Add Criteria
Add criteria to filter by user, date range, table name, or action type.
Create Report
Create report based on query for printable audit trail.
Excel: Create Audit Dashboard
Create Pivot Table
Insert → PivotTable from AuditLog data.
Add Filters
Add User, Sheet, Date as filters for easy filtering.
Add Summary
Show count of changes by user, by date, or by sheet.
Create Charts
Visualize audit data with charts showing change trends.
Part 5: Security Best Practices
Protect Audit Logs from Tampering
Separate Database
Store audit log in separate Access database with restricted access.
Encrypt Database
Use Access database encryption or Windows encryption for audit log files.
Regular Backups
Backup audit logs regularly to prevent data loss.
Archive Old Logs
Archive old audit entries to separate database to maintain performance.
Compliance Considerations
HIPAA Compliance
Ensure audit logs meet HIPAA requirements for healthcare data access tracking.
SOX Compliance
Financial data changes must be tracked with user identification and timestamps.
Retention Policies
Establish how long audit logs must be retained based on regulations.
Access Controls
Limit who can view audit logs to authorized personnel only.
Troubleshooting Common Issues
Issue: Audit Log Not Recording Changes
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.
Issue: Performance Degradation
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.
Issue: Users Bypassing Audit Log
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.
Additional Resources
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.
Is Your Data Secure?
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 →