Audit Trail - Deleting records

[irish634]

I've always used an audit trail for tracking changes of records that already exist but I have had a situation come up where I now need to track records that have been deleted. For example, if I delete a record in the datasheet view.

I've seen several examples how to do this, with the most common seeming to store the data into a temporary table while waiting for the delete confirmation. If I am reading the notes correctly, it's done this way to capture occurrences when multiple records are selected to be deleted. Is this the best approach for this type of task?



Are there any other suggestions?

[Rod]

You may want to investigate the 'BeginTrans, Rollback, CommitTrans' functionality which is there specifically to handle mutiple-record updates.

[stmoong]

Personally, I use an audit trail table to store records that have been added, updated and deleted for each table that require audit trail. For update (edit), there will always be two records in the audit trail table to store information before and after that update. A flag will determine what operation was performed, user and timestamp columns are added for informational purpose.

Like what Rod mentioned, I use transaction to ensure both the data table and its correponding audit trail table are updated properly.

===
Additional information: I'm using unbound forms when doing the above.
===

Allen Browne has other method here.

[irish634]

Thanks. I was referring to Browne's method as what I found as common. I'll look into transaction and go from there. I never had a need to log added records, I just use two extra fields: 'created by' and 'date' in the actual record. Then I just tracked changes/edits. I recently had an admin delete records in a subform in datasheet view and had no way to reconstruct it. Browne's method is simple enough to implement, but I was looking at better/alternative methods

[ssanfu]

Here is another example of tracking changes http://access.mvps.org/access/modules/mdl0021.htm

And at Rogers Access Library
intermediate audit trail version http://www.rogersaccesslibrary.com/forum/topic399.html
advanced audit trail version http://www.rogersaccesslibrary.com/forum/topic398.html

[irish634]

If I am understanding correctly, transactions are best used with 'unbound' forms?

In a nutshell my audit trail is pretty much this here. I've modified it a little over the years but the premise is the same. It is usually called in the form's before update event. I've amended the code to capture deleted data by making the new data "#Deleted#". I can call it in the form's delete event and it captures each record I delete perfectly.

The problem I have is this: The 'form delete' event fires for each record that is being deleted. The form is in datasheet view so multiple records can be selected and deleted at once and the data is logged whether the user cancels or not.

That's all ok given I don't expect data to be deleted that often, but I'm not that sure where I should begin the transaction in the event I want to roll back...

[stmoong]

If you are logging audit trails using Events, similar to Browne's approach, then storing the multiple records to be deleted in the temporary table would be workable.

I would assume that this temporary table be in the front end database rather than the backend database, unless you also track the transaction, so that you know what records to be restored in case the delete is cancelled.

From Browne's article,

In the form's Delete event, the code below writes a copy of the record to the temp table. In the form's AfterDelConfirm event, these records are copied into the true audit table only if the Status argument provided by Access indicates that the deletion proceeded. The copies in the temp table are then removed.

You basically check the Status to see if you need to add to the audit table or not. The records in the temp table are deleted regardless if cancelled or committed during delete. So, wrapping these two in a transaction may make sense.

I am not clear how it would work for batch delete in the above case, seems it looks like using the Events approach, it's deleted per record basis? Or maybe someone can clarify this further.


I use transaction with unbound forms because I find it more straight forward, have clearer transaction control with batch updates, and I didn't want to create a temporary table for each audit trail table.

[Rod]

If I am understanding correctly, transactions are best used with 'unbound' forms?

Basically yes.

The update of multiple records really only occurs in manipulating recordsets within VBA code, a batch processing approach. In such situations the BeginTrans, CommitTrans and Rollback functions are very useful.

You mention the possibility of selecting more than one record for deletion in datasheet view which causes multiple event triggers. From what I understand you wish to cancel the delete and update the record instead with a marker. Why does this cause you problems? What am I missing?

I would assume that this temporary table be in the front end database ...

A FE temporary table is definitely the more efficient solution.

...seems it looks like using the Events approach, it's deleted per record basis? Or maybe someone can clarify this further.

Don't know. I would imagine this to be so but would have to investigate further to confirm it.

[irish634]

You mention the possibility of selecting more than one record for deletion in datasheet view which causes multiple event triggers. From what I understand you wish to cancel the delete and update the record instead with a marker. Why does this cause you problems? What am I missing?

Using a "deleted" marker was suggested, not something I was interested in doing at the moment. I can certainly do that, but I was just thinking I could expand on my current audit trail function and work with that. I'm not so sure transactions will work for me with the way I am currently handling the audits. I may need to rethink my approach.

A FE temporary table is definitely the more efficient solution

Won't this cause the FE to bloat?

Unless I am not capturing something, I also think I found a bug using my current method. If I delete 1 or more records, it captures and logs everything. If I "cancel" a deletion, then try again to delete multiple records, only the first record selected is captured and logged. I have to test some more.

[sssandhya89]

Hey Irish,
I am Sandhya, new to MS Access, i am trying to create an audit trail function for a data set i have in MS Access, i have a user logging in function, i need to know who edited and what did they edit in the record, can you please tell me how did you create your audit trail function so that i can use it as reference may be?

Sandhya