MS Access "record changes" log

2
votes

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 ta form level, insert the record "Before" but only Update "After". Again, problem with multiple deletes.

3
ms-accessvba
what is usually done (and easy to manage) is to keep a log of sql INSERTs, UPDATEs or DELETEs. But I guess you are working here with recordsets which are bound to controls\forms, aren't you? By the way, what do you want to track exactly? Modifications on all tables? a few tables? specific fields? per user? - Philippe Grondier
Another thing to consider up front is if/how you will present this audit trail to users. It's incredibly difficult to end up with a solution that is flexible, concise, and easy for users to understand. You generally have to pick the two most important attributes from that list and sacrifice the third. That decision will impact your entire design, so it's important to figure that out up front. - mwolfe02
Would it help to re-define the concept of "deleted" to something like "active"? The form's record source could include WHERE active = True And deletions actually become active = False Seems like this could be an inexpensive way to maintain a history of deleted records --- they're still present, but just not visible in the form. - HansUp
+1 to @HansUp for the idea of "marking" records deleted, rather than actually deleting them. As storage gets cheaper, I'm reconsidering what I should really be deleting. As long as you can maintain performance, it's nice to be able to tell users, "Oh, you didn't mean to delete those records? Actually, yes, I can get them back for you." - mwolfe02
instead of an IsActive field, you could have a DateDeleted field. It eats up a bit more storage space, but it could be indexed and would also provide additional information (which might be helpful someday when trying to figure out exactly what a user was doing). Every query would then have a DateDeleted Is Null condition, instead of Active = True. - mwolfe02

3 Answers

3
votes

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.

3
votes

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.

0
votes

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