Audit Trail for a table

[muralikreddy]

Hi,

I want to capture the audit trial for the changes made to the records of a table to a seperate audit trial table. Could some body please help me to do this.

Thanks & Regards,

 

[pbaldy]

There are several methods; search on "audit trail". In Access, it will need to be done at the form level, as there are no triggers available at the table level.

 

[gemma-the-husky]

i have an audit table (auditdata) with several fields - the fields are described in the sqlstring

i use this function to add items to it - i just call this with a suitable itemtext
eg "field changed from 12 to 13" - the routine adds who did it and when

there are a couple of functions in here (getcomputername etc), that wont exist in your dbs - either ignore them or use your own.

the audticleared flag is to enable another user to review the audit trail, and "review and pass" new items.

Sub auditme(ItemText As String, Optional auditclass As Long, Optional showerror As Boolean)
Dim mycomp As String
Dim mylogin As String
Dim sqlstrg As String

mylogin = CurrentUser
mycomp = GetComputerName

If IsMissing(auditclass) Then auditclass = 0
If IsMissing(showerror) Then showerror = False

sqlstrg = "insert into audit_data (audittype,auditdetails,auditdate,auditlogin,auditterminal,auditcleared) " & _
    "select " & _
    auditclass & ", " & _
    Chr(34) & ItemText & Chr(34) & " , " & _
    "#" & Date & "# ," & _
    Chr(34) & mylogin & Chr(34) & " , " & _
    Chr(34) & mycomp & Chr(34) & " , " & _
    False

On Error GoTo fail
currentdb.Execute sqlstrg, dbFailOnError

exithere:
    Exit Sub
    
fail:
     If showerror Then MsgBox ("Error: System was unable to record this action in the audit trail. " & vbCrLf & vbCrLf & _
        "Error: " & Err & "   Desc: " & Err.Description)
    Resume exithere


End Sub