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.

What is an Audit Log?

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.

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 VBA code:vba

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 changes:

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.

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.

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