Audit Trail database

[PhilaG]

Good Day,



We are trying to develop a functionality on our system/ Database.

The user is looking for audit report, which will audit everything that any user is doing on the system or database. because the system is used by many users, now it is difficult to track how did what. I have tried to use some code that i found on the internet but never worked. tried to follow some tutorial on internet with on success.

Sub AuditChanges(IDField As String, UserAction As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                .Update
            End With
    End Select
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

I would appreciate you assistance
Thank you

[Micron]

Please go back to your post (if it's not too late) and encapsulate that in code tags, and use these tags and proper indenting for anything more than a few lines (# on thread toolbar). I might not be able to help with the problem, but I can at least help you to help others make sense of that. I won't even try (I know, I'm old and intolerant but that's the way it is).

[orange]

You may get some ideas from this thread. (data macro based)
Or this one (forms based).
You might also want to review SQL server Express (free) as your back end database.

Whatever you decide, take the time to test thoroughly before putting any audit function into production if you are dependent on audit records.

[ssanfu]

You might also see Creating an Audit Log by Allen Browne ( article and code)

[CJ_London]

'never worked' doesn't help us to help you. You need to provide the context of when you run this code, the nature of the form (single/continuous/datasheet), whether the form has subforms, any error messages etc.

and when you step through the code - do you see the values you expect to see?