How to Create an Audit Log in MS Access

Maintaining the integrity of your database is essential, especially when multiple users access and modify the data. An audit log in MS Access helps track changes, providing transparency and accountability by recording who made changes, when, and what was changed. This tutorial will guide you through creating an audit log step by step.

An audit log, also known as a change log or history table, is a system that records changes made to a database. It captures:

• The user who made the change.
• The date and time of the change.
• The table, field, and data involved in the change.

Without a built-in audit trail, Microsoft Access gives you no native way to track who changed a record, what the old value was, or when it happened. This becomes a serious problem in multi-user environments, compliance-sensitive industries (healthcare, finance, legal), or any database where data accuracy is critical.

• Compliance: Meet regulatory requirements by keeping a complete history of data changes.
• Accountability: Know exactly which user made a change and when.
• Data Recovery: Restore previous values if a record is incorrectly modified or deleted.
• Audit Trails: Provide evidence during internal or external audits.
• Security: Detect unauthorized or suspicious data modifications early.

A properly implemented audit log in MS Access records every meaningful data change. At minimum, your audit log should capture:

• User Name: The Windows login name of the person who made the change (via Environ("UserName")).
• Date & Time: The exact timestamp using Now().
• Table Name: Which table was affected.
• Field Name: The specific field that was changed.
• Old Value: The value before the change.
• New Value: The value after the change.
• Action Type: Whether the change was an Insert, Edit, or Delete.

Steps to Create an Audit Log in MS Access

1. Set Up the Audit Log Table

Create a table in MS Access to store the audit log information.

• Name the table `tblAuditLog`.
• Add the following fields:
  • ID: AutoNumber (Primary Key)
  • ChangeDate: Date/Time
  • UserName: Short Text
  • TableName: Short Text
  • FieldName: Short Text
  • OldValue: Long Text
  • NewValue: Long Text

2. Create a Form for Data Entry

Set up a form for users to enter data.

• Open the desired table in MS Access.
• Click Create > Form to generate a form automatically.
• Save the form as `frmDataEntry`.

3. Write VBA Code for the Audit Log in MS Access

To track changes, use VBA to log the changes into `tblAuditLog`.

Here's how to do it:

• Open the VBA Editor
  • Press Alt + F11
  • In the VBA Editor click Insert > Module
• Add the following code to the VBA module:

Public Sub AuditChanges(tblName As String, fldName As String, oldVal As Variant, newVal As Variant)
    On Error Resume Next
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblAuditLog")
    rst.AddNew
    rst!ChangeDate = Now()
    rst!UserName = Environ("UserName") ' Captures the current user's Windows login name
    rst!TableName = tblName
    rst!FieldName = fldName
    rst!OldValue = oldVal
    rst!NewValue = newVal
    rst.Update

    rst.Close
    Set rst = Nothing
    Set db = Nothing
End Sub

4. Attach VBA to Form Events

To log changes, attach the `AuditChanges` procedure to your form's BeforeUpdate event.

1. Open the form in Design View.
2. Select a field and open its Properties.
3. Under the Event tab, find BeforeUpdate.
4. Add the following VBA code to log each change.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctl As Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Then
                Call AuditChanges(Me.RecordSource, ctl.Name, ctl.OldValue, ctl.Value)
            End If
        End If
    Next ctl
End Sub

5. Test the Audit Log

• Open your form (frmDataEntry) and modify some data.
• Check the tblAuditLog table to confirm that the changes are being logged.

How to Track Deleted Records in MS Access Audit Log

The BeforeUpdate event only captures edits. To also log deleted records, attach the following VBA to your form's BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Call AuditChanges(Me.RecordSource, "RECORD DELETED", Me.[YourPrimaryKeyField], "")
End Sub

Replace [YourPrimaryKeyField] with the actual primary key field name of the table you are auditing (e.g., Me.[CustomerID]). This logs the record's ID before deletion so you have a traceable history.

Viewing and Querying Your MS Access Audit Log

Once your audit log is running, you will want to query tblAuditLog to review changes. Here are useful SQL queries you can run directly in the Access Query Designer:

Find all changes made by a specific user:

SELECT ChangeDate, UserName, TableName, FieldName, OldValue, NewValue
FROM tblAuditLog
WHERE UserName = "john.smith"
ORDER BY ChangeDate DESC;

Find all changes made to a specific field in the last 30 days:

SELECT ChangeDate, UserName, FieldName, OldValue, NewValue
FROM tblAuditLog
WHERE FieldName = "Price" AND ChangeDate >= Date() - 30
ORDER BY ChangeDate DESC;

MS Access Audit Log vs Built-in History Features

Microsoft Access does not have a built-in field-level change tracking feature like SQL Server's temporal tables. The comparison below shows what you get natively vs. what a custom VBA audit log provides:

• Native Access: No record-level change history, no old value tracking, no user attribution.
• Custom VBA Audit Log: Full field-level history with user name, timestamp, old and new values.
• SharePoint Integration: Offers basic version history but only for linked lists, not full Access tables.
• SQL Server Backend: If you upsize to SQL Server, temporal tables and triggers provide robust audit trails natively.

For most Access desktop databases, a custom VBA-based audit log as described in this guide is the most practical and flexible solution.

Best Practices for Using an Audit Log in MS Access

• Archive Old Logs: Regularly archive older records from the audit log to maintain database performance.
• Secure the Audit Table: Restrict access to the `tblAuditLog` table to ensure data integrity.
• Customize Fields: Add additional fields like IP address or application name if needed.

Common Audit Log Mistakes to Avoid in MS Access

• Not logging deletes: BeforeUpdate only catches edits. Add BeforeDelConfirm to capture deleted records.
• Using On Error Resume Next silently: The AuditChanges sub uses On Error Resume Next — make sure you also log errors separately so silent failures don't go unnoticed.
• Forgetting to audit all forms: Each form that allows data entry needs its own BeforeUpdate event wired to AuditChanges.
• No index on ChangeDate: As tblAuditLog grows, queries slow down. Add an index on the ChangeDate field for faster lookups.
• Storing audit log in the same file: In a split database, keep tblAuditLog in the backend (data) file, not the frontend, to avoid bloating the front-end .accdb.

Conclusion

By implementing an audit log in MS Access, you can track changes effectively, ensuring data integrity and security. The combination of a well-structured table, VBA automation, and proper testing makes this approach robust and practical for real-world applications.

Ready to streamline your business with our MS Access Custom Database Services? Don't let outdated or inefficient systems hold you back. Contact us today to discuss your requirements and receive a free consultation.

Also Read: Modify Access Database Report | Custom Dashboard in MS Access | Automate Reports in MS Access

Can MS Access track changes automatically without VBA?

No. Microsoft Access does not have a built-in automatic change tracking feature at the field level. You must implement it yourself using VBA with form events like BeforeUpdate and BeforeDelConfirm, as shown in this guide.

What is the difference between an audit log and an audit trail in Access?

They refer to the same concept. An audit log (or audit trail) is a table that records every data change — capturing the user, timestamp, field, old value, and new value for each modification.

How do I find out who deleted a record in MS Access?

By adding a BeforeDelConfirm event to your form and calling AuditChanges inside it, you can record the user name and timestamp of every deletion. Without this, Access provides no native way to track deleted records.

Will the audit log slow down my MS Access database?

For most databases, the performance impact is minimal. To keep it fast: add an index on the ChangeDate column in tblAuditLog, archive old records periodically, and store tblAuditLog in the backend file of a split database.