In response to a question posted here recently, I started thinking about how to implement an audit log which would allow the user to log inserts, edits, and deletions from tables within Access. Prior to the advent of Data Macros, this process had to be done via code, and had some pitfalls, as documented by the Access MVP Allen Browne on his website. But in the code on Allen's website, he writes an entire record to his audit table every time even a single field was changed; this means that you need a separate audit log table for each table you need to audit.
My goal was to create a relatively simple process which would meet the needs of the vast majority of people and which could be implemented quite simply. To do this, I came up with a single audit log table which contains the following fields.
The fields in this log table are defined as:
In order to implement this Audit log, you only need to add a couple of lines of code to the form(s) used to edit your tables and you will be on your way. When inserting or editing a record on your form, you would use the Form_BeforeUpdate and Form_AfterUpdate events, when deleting a record, you would need to add code to the Form_Delete and Form_AfterDeleteConfirm events.
As Allen Browne highlighted on his site, you cannot simply write the records to your audit log in the BeforeUpdate and Delete events because each of these actions can be cancelled by the user after the data is written to the log. To resolve this I first write the information to tbl_Audit_Log_Temp, and then in the AfterUpdate and AfterDeleteConfirm events, if the Update or Delete operations were not cancelled, I copy the data from tbl_Audit_Log_Temp to tbl_Audit_Log and then delete the data from the temporary table.
The only line you will need to add to your Form_BeforeUpdate event is highlighted in the code segment below. This should be the last line of code in the event procedure and should only be executed if all of the error checks and field validation checks have passed.
Private Sub Form_BeforeUpdate(Cancel as Integer) on Error goto ProcError 'Enter other error checking code here 'do not allow code execution to reach this line if you are canceling the Update operation Call AuditLog(Me, "tbl_Audit_Test", IIf(Me.NewRecord, "Insert", "Edit"), "ID") ProcExit: Exit Sub ProcError: 'process errors here Cancel = true End Sub
To log record deletions, you need only add a single line of code to the Form_Delete event, as indicated below.
Private Sub Form_Delete(Cancel As Integer) Call AuditLog(Me, "tbl_Audit_Test", "Delete", "ID") End Sub
Then, to ensure that edits or deletion operations are actually logged, you need to call the AuditLogConfirm function with the following code. The value of the Status argument in the AfterDelConfirm event will be either 0 (deletion confirmed) or 2 (deletion cancelled), so I wrote this procedure to accept a zero as the Confirmed value.
Private Sub Form_AfterUpdate() AuditLogConfirm (0) End Sub Private Sub Form_AfterDelConfirm(Status As Integer) Call AuditLogConfirm(Status) End Sub
The real guts of this process is the AuditLog subroutine, which requires parameters for the name of the table being edited, the action being performed (Insert, Edit, Delete), and the name of the Primary Key (PK) field used in the table being audited (the pimary key must be a single, numeric value, preferably an autonumber).
Public Sub AuditLog(frm As Form, TableName As String, Action As String, PKFieldName As String) Dim dtAuditAt As Date Dim strAuditedBy As String Dim pkValue As Long Dim bRecord As Boolean Dim db As DAO.Database Dim rs As DAO.Recordset Dim fld As DAO.Field dtAuditAt = Now() strAuditBy = fOSUserName() Set db = CurrentDb strSQL = "SELECT * FROM " & IIf(Action = "DELETE", "tbl_Audit_Log_Temp", "tbl_Audit_Log") _ & " WHERE 0 = 1" Set rs = db.OpenRecordset(strSQL,, dbFailOnError) dtAuditAt = Now() strAuditedBy = fOSUserName() pkValue = frm(PKFieldName).Value For Each fld In frm.RecordsetClone.Fields If (fld.Name = PKFieldName) Then bRecord = False ElseIf (Action = "Insert") Or (Action = "DELETE") Then bRecord = True ElseIf (Nz(frm(fld.Name).OldValue, "") <> Nz(frm(fld.Name).Value, "")) Then bRecord = True Else bRecord = False End If 'If you are auditing a table with memo or OLE fields, then do not attempt 'to add those values to the FieldValue field in the Audit Log If (fld.Type = dbMemo) Or (fld.Type = dbLongBinary) Then bRecord = False End If If bRecord Then rs.AddNew rs!Action = Action rs!TableName = TableName rs!ActionBy = strAuditedBy rs!ActionDT = dtAuditAt rs!RecordID = pkValue rs!FieldName = fld.Name rs!FieldValue = CStr(Nz(frm(fld.Name).Value, "")) rs.Update End If Next rs.Close Set rs = Nothing Set db = Nothing End Sub
The code initially opens the Audit Log Temp table, and then loops through the list of fields bound to the form via the forms RecordSource property. The code determines which fields to write to the audit log (If the action is an Insert or Delete operation all fields get logged; if it is an Edit operation only those fields whose values have changed during the current operation are written to the log) and writes those values, along with the table name, ActionBy, ActionDT, and RecordID values.
The AuditLogConfirm procedure simply copies the records from the temporary table to the permanent Audit Log, if the value passed into the procedure is a zero (0). Otherwise, it skips that section of code and deletes the records in the temporary table.
Public Sub AuditLogConfirm(Confirmed As Integer) Dim strSQL As String Dim db As DAO.Database Set db = CurrentDb If Confirmed = 0 Then strSQL = "INSERT INTO tbl_Audit_Log (TableName, Action, ActionBy, ActionDT, RecordID, " _ & "FieldName, FieldValue) " _ & "SELECT TableName, Action, ActionBy, ActionDT, RecordID, FieldName, FieldValue " _ & "FROM tbl_Audit_Log_Temp" db.Execute strSQL, dbFailOnError End If strSQL = "DELETE FROM tbl_Audit_Log_Temp" db.Execute strSQL, dbFailOnError Set db = Nothing End Sub
What I think is neat about this method of storing the data is that we can now write queries against the audit log to view the changes we have made to each record over time. You can do this with the method Allen uses as well, but it is much more difficult to determine what values have changed. With this method, and the use of a cross-tab query, only the values that have changed are displayed on each row, and the value displayed is the new value. The sample database contains several records with a series of field changes. You can see those changes in the results of qry_Audit_Log_XTAB:
Big, bold, NOTE: This technique will not work if you are running a DELETE, INSERT, or UPDATE query. In order to implement a procedure similar to this in a query, I believe you would have to develop this functionality in a data macro, would have to check for each field in the table independently (no looping through field names) and have rows in the data macro for each of the fields being written to the table. This would be a lot of work!!!
Hope you find this article and this technique useful.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.