Project Perfect Project Management Blog

There are a number of approaches to recording history in Microsoft Access.  We have recently been looking at how to apply them to an existing application.  The goal is to record who made what changes and when they made them.  Sort of an audit facility for Access.  Basically you want to capture:

• New records
• Changes to existing records
• Deletion of records

It might seem a waste of time to think of it as three separate activities but you need to handle them differently for recording history.

• New records only have an ‘after’ record.
• Changes have a ‘before’ and ‘after’
• Deletions only have a ‘before’

Here are the three main approaches.

1. Use the OldValue and Value  of the control triggered by a BeforeUpdate event.  This works where you are adding or updating a record.  Use the OnDelete event to capture deletions.
2. Create a temporary table of values before you carry out any changes, then compare it with values in the table after update.  Write the differences to a history file.  This is good where you are doing a bulk update.  For example, if you have a price list, and are updating all prices from a particular supplier by x%.  You create a copy of the price table before changes, then compare it with the table after update.  Write the differences to a history file.
3. Create custom writes to a history table for particular events.  An example may be where you are creating a copy of a record such as a user profile.  You copy all the values of an existing person to a new person record.  Once established, you might make modifications to the person record.  In this case you create a specific SQL statement to write the new record details to your history file.  Another example might be where you are updating some system parameters that are held in the registry.  They are not in Access so you need to capture the current registry entry, the changes, and write them to a history file.

Another consideration is that history files can get big.  Given you are capturing everything from integers to memo fields, the first instinct is to cater for the biggest.  You make the old value and new value fields memos.  Unfortunately you take up the room for a memo in Access.  If there is one or five thousand characters, the space taken up is the same.  The trick is to have two history tables.  One has old and new values as text and the other as memo.  Run code to check the length of the old and new values, and if too big, write to a the table with the memo field.  For reporting purposes, you can combine the two.

In subsequent blogs, we will cover how to actually achieve what we are talking about here.  The purpose of this blog is to get a helicopter view of capturing history in Access.  Once you understand the terrain, you can better understand the wealth of information available from other resources.