Tracking what users do?

[Singh400]

No idea where to post this, please move if in wrong place…

I need a method to track what users do in the database. Nothing too detailed. Just something, that makes a log like:

USER created record on <date/time>
USER modified record on <date/time>
USER deleted record on <date/time>

This only needs to be seen by a few select people.

I plan to implement User Level Security, so everyone will have their own usernames and passwords. Don’t worry, I have read the ULS PDF

Thanks for any help!

 

[KenHigg]

I would create a function that appends records to a log table. You send the function data like user name, date/time and maybe code of what they did. The function would then write this data to the new record. Then you call the function from where ever you want to, maybe on a form open or on a record update event...

 

[Singh400]

Oh crap. That sounds alot more complicated than I like. Any samples floating about that I can take apart?

It's not something that is absolutely needed. Thought it would be a nice feature to have.

 

[namliam]

Have a search on the forum for "Audit trail" that should net you some hits.

Doing a proper audit trail is rather involved!

 

[Singh400]

Have a search on the forum for "Audit trail" that should net you some hits.

Doing a proper audit trail is rather involved!

As long as it makes me look busy! Cheers for the help fellas. Off to search for 'Audit Trail'. I shall be back to pester you when I get stuck (and I will )

 

[namliam]

Happy to help you along WHEN you do get stuck...

The samples though should get you started real good.

 

[KenHigg]

Oh crap. That sounds alot more complicated than I like. Any samples floating about that I can take apart?

It's not something that is absolutely needed. Thought it would be a nice feature to have.

A simple one can be made real easy. I'll whip a sample up for you. Give me a little while to find a few free minutes...

 

[gemma-the-husky]

i have a table called audit_data, to store whatever i need - the fields in this are as specified in the sql string just below, and a table called audit_types to store classes of auditdata (eg 0=general, 1=special, 2=systemchanges)

then in any after update event etc that i want to trap i call this sub by

auditme ("messagetext",0,false)

which inserts messagetext, as message type 0 , without showing errors

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 'a function to retrieve this computer name - not realy necessary

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

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

'note that among these fields, i use the auditcleared flag later, to review the audited events, and flag them as inspected.
On Error GoTo fail
currentdb.Execute sqlstrg, dbFailOnError

exithere:
    Exit Sub
    
fail:
'if you suppress this message (or call the sub with showerror false), then any auditfailure just wont write to the audit trail, without causing any programme notification
'neither will success, so your users may not even realize you are tracking some changes/events

     If showerror Then MsgBox ("Error: Unable to record this action in the audit trail. " & vbCrLf & vbCrLf & _
        "Error: " & Err & "   Desc: " & Err.Description)

'    Resume exithere
End Sub

i then have a form to review the ausit data, and set each message as inspected.

 

[irish634]

I put my audit trail sample in the code repository
Give it a try and see how you like it.

 

[KenHigg]

Here's a real simple example using a method that is very flexible.

 

[Singh400]

Ooo blimey. Alot of information to plow through here. Will get back to you when I've had a chance to have a proper look at it. Thanks to everybody.