How to Create a History Log for Field Changes in Microsoft Access
Question details
The user needs a method to track and preserve previous field values in Microsoft Access instead of overwriting the existing data when updates are made.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Tracking historical data changes and preserving previous records during database updates.
- Observed behavior
- Currently, updating a field overwrites the original data; the desired behavior is to securely store each modification, including the old value and date, in a related audit or history table.
Before making structural changes or writing macros, ensure you have full administrative rights to the database and create a backup copy of your Access file (.accdb) to prevent accidental data loss during testing.
Use Data Macros to Create an Automated Audit Log
Data macros are the most reliable and modern method for tracking changes at the table level, ensuring logs are created even if data is updated outside of a form.
Introduced in Access 2010, Data Macros operate directly at the table level (similar to SQL triggers). This ensures that no matter how a record is updated—whether via a form, query, or direct table edit—the change history is reliably recorded.
Create a new table named 'HistoryLog' with fields to store the audit trail: LogID (AutoNumber), RecordID (Number), FieldName (Short Text), OldValue (Short Text), NewValue (Short Text), and ChangeDate (Date/Time).
Navigate to the Navigation Pane, right-click the main table whose fields you want to track, and select 'Design View'.
Switch to the 'Table Design' tab on the ribbon, click 'Create Data Macros', and choose 'After Update' from the drop-down menu.
Use the 'If' block to check if the specific field has changed using the condition `Updated("YourFieldName")`. Inside the block, add a 'CreateRecord' action pointing to the 'HistoryLog' table. Set the fields using `[Old].[YourFieldName]` for the original value and `[YourFieldName]` for the new value.
Implement a VBA BeforeUpdate Event with an INSERT Statement
For complex tracking requirements or older Access versions, you can use a VBA script within a form's BeforeUpdate event to log changes via SQL.
Use a Linked Subform for Manual History Entries
A simpler, non-programmatic approach involves creating a linked child table and displaying it as a subform to log history records manually.
Need a Lightweight Office Suite? Try WPS Office
While WPS Office does not feature a standalone relational database tool like MS Access, it provides powerful, free alternatives for Word, Excel, and PowerPoint. If you rely on spreadsheets for data tracking, auditing, and version control, WPS Spreadsheet offers robust change tracking and seamless compatibility with Microsoft Excel formats.
- 1. Download the Installer: Visit the official WPS Office website to download the free installation package.
- 2. Install the Suite: Run the setup file and follow the on-screen instructions to install WPS Office.
- 3. Import Your Data: Export your Access data to CSV or Excel formats, and easily open them in WPS Spreadsheet for further tracking and analysis.

Frequently Asked Questions
What is the most reliable way to track changes in an Access database?
The most reliable method is using Data Macros attached directly to your tables. By using 'After Update' data macros, Access automatically records changes in the background regardless of whether the update occurred through a form, a query, or a direct table edit.
Can I track the specific user who made the change in Microsoft Access?
Yes. When setting up your history log table, include a 'ChangedBy' field. You can then use the `CurrentUser()` function (if using Access security) or Windows API calls in VBA to capture and record the username of the person making the modification.
Why use an Audit Log instead of simply overwriting data?
An audit log prevents the permanent loss of historical information. It ensures data integrity, helps comply with regulatory standards, and provides a clear, verifiable trail of exactly when a field was updated and what its previous values were.
Will tracking field history slow down my Access database?
While extensive logging can increase the size of your database file (.accdb) over time, the performance impact is usually minimal for small to medium databases. To maintain optimal speed, it is recommended to periodically archive or clean up old history logs before your database approaches the 2GB size limit.




