Creating an audit trail

[txaccess]

Would this be possible (or even desirable)? I would like a single audit table that records any change made to data by any user on any form. The table would be something like:

AuditRef - PK
TableName - originating table of the field
FieldName - name of the changed field
OrgVal - Original value
NewVal - New field value
ChangeBy - user name who changed the field
ChangeDate
ChangeTime

I don't want an audit table for each form, rather a single table that records all record changes as described above. I could build a purge facility to rid the table of records every now and again.

Don't forget, the change made could be on any form or any table and would update the audit log table.

Any thoughts as to how or if this is possible?

Thanks in advance.

I haven't failed, I have just found 10,000 ways that it won't work!

 

[MichaelRed]

see the faq181-291. It provides a close approximation of your stated desire. Be aware that Ms. A. does NOT support record events, so a related requirement to have this be effective is to strictly control the security and require all access to tables to be via bound forms.





MichaelRed

 

[BobStubbs]

First, here is my function to write an audit record. This is placed in a global module, and can be called from anywhere, as needed. Hopefully, the parameter variable names make sense - my audit table has these columns:

TableName - table being changed
FieldName - field being changed
RecordKey - primary key of the record being changed
OldValue - old value of field
NewValue - new value of field
ChangeDate - change date
ChangedBy - current user name

It also contains some code to handle e.g. surname changes, as otherwise passing O'Neill etc as an old or new value messes up the SQL, due to the single quote character.

Function WriteAuditRecord(strTableName As String, strFieldName As String, _
    strRecordKey As String, strOldValue As String, strNewValue As String)

Dim strLogDate As String
Dim strSQL As String

strLogDate = Format$(Now, "dd/mm/yyyy")

'----------------------------------------------------
'- Fix any single quote characters in strings, as   -
'- these cause an SQL error                         -
'----------------------------------------------------
    strOldValue = FixSingleQuote(strOldValue)
    strNewValue = FixSingleQuote(strNewValue)
    strRecordKey = FixSingleQuote(strRecordKey)
    
'----------------------------------------------------
'- Write the audit record                           -
'----------------------------------------------------
    DoCmd.SetWarnings (False)
    
    strSQL = "INSERT INTO tblAuditTrail (TableName, FieldName, RecordKey, OldValue, NewValue, ChangeDate, ChangedBy) "
    strSQL = strSQL & "VALUES ('" & strTableName & "', '" & strFieldName & "', '" & strRecordKey
    strSQL = strSQL & "', '" & strOldValue & "', '" & strNewValue & "', '" & strLogDate & "', '" & CurrentUser & "')"
        

    DoCmd.RunSQL (strSQL)

    DoCmd.SetWarnings (True)

End Function

Function FixSingleQuote(strOneLine As String) As String

Dim I As Integer
Dim strOutLine As String

If Len(strOneLine) < 1 Then
    FixSingleQuote = ""
    Exit Function
End If

For I = 1 To Len(strOneLine)
    If Mid$(strOneLine, I, 1) = "'" Then
        strOutLine = strOutLine & "''"
    Else
        strOutLine = strOutLine & Mid$(strOneLine, I, 1)
    End If
Next I

FixSingleQuote = strOutLine

End Function

2. I impose a 'forms only' discipline in my databases, so users never change tables directly.

I can then call my 'WriteAuditRecord' function as required, usually from code behind a [Save record] button, or from the form's Before update event. The strcture to call it looks like this:

    If FieldName.OldValue <> FieldName Then
        strResult = WriteAuditRecord("tblTablename", "Field name", RecordKey, FieldName.OldValue, FieldName)
    End If

I compare FieldName.Oldvalue with FieldName for each field which I wish to audit; if they differ, I write an audit record.


Bob Stubbs

 

[txaccess]

Thanks Bob - I appreciate your help & the code sample.

I haven't failed, I have just found 10,000 ways that it won't work!