MS Access "record changes" log

Question

Whenever a record is deleted or updated on a form, I want to save its old values in a history table (let's call it Revised). I guess I have to do the following:

For record changes:

• use the BeforeUpdate event to save the data somewhere (collection ? array ? or to a recordset -the Revised table- without saving-yet ?)
• use the AfterUpdate event to add/save that data to the Revised table

For Deletions:

• use the OnDelete event to save the data - but again how ? several records could be deleted at once since the form (a subform in fact) is in datasheet view
• use the AfterDelConfirm to add that data to the Revised table.

Do you have any clues, comments or links for this ?
This is all in a "pure Access" (no SQL Server) at the moment. Many thanks !

---

Edit: as usual, properly asking the question gaves me ideas:

• option 1
  use the BeforeUpdate or the OnDelete to build the SQL statement, and use the AfterUpdate or the AfterDelConfirm to Execute the SQL statement. But that won't work for multiple deletions ?

• option 2
  have the Revised recordset defined at form level, insert the record "Before" but only Update "After". Again, problem with multiple deletes.

Answer 1

Another approach I have considered more recently, but have not had an opportunity to actually implement, would be to use transactions to perform the change tracking. The basic algorithm would be:

1. use BeginTrans on the workspace prior to making any changes
2. in the OnDelete event
   • perform the deletions in code executing Delete queries against the workspace from step 1
   • add a record to your change auditing table
3. in the BeforeDelConfirm event
   • set Cancel = True
   • display your own Confirmation dialog
   • if user confirms then CommitTrans on workspace
   • otherwise Rollback the transaction on the workspace

Similar approach for Updates/Inserts. This would avoid the need for temporary tables/arrays/collections, etc. but I haven't fully thought through everything. The devil may be in the details.

Answer 2

I've successfully used a variation of Allen Browne's approach in a couple of different projects. Check out his website for more details:

Creating an Audit Log

His solution uses temp tables and four generic function calls to handle the issue with multiple deletes.

Answer 3

An "easy" and generic solution, which could be implemented for multiple tables, would be to have a tracking table, made of the following:

Track_Table
==================================================
id_track as primary key
id_table as name of the table which has been updated
id_primaryKey as the record identifier (the PK of the updated record)
changeType, being either DEL or UPDATE
changeDate, as dateTime value
fieldName, as text
oldValue, as text or memo
newValue, as text or memo

if you have to identify the user who did the update, just add

userId

in your table ...

You could then create some generic "before update" and "after update functions" to be called on selected form's beforeUpdate and afterUpdate events. The beforeUpdate fonction will store the old value in a variable, while the afterUpdate function will populate the missing data and insert a new record in the track table.

You will have to find a way to find out the right\corresponding table name and field name. This could be difficult if you are using views or field aliases to display your data in forms.

Of course, all tables to be followed must have a primary key so you can follow changes at the record level. PKs set on multiple fields will surely be problematic ....

oldValues and newValues will have to be converted as text so you can store them in a text or memo field