Aya via AccessMonster.com said:
Hi, I'm trying to figure out if Access can create a tracking which can
tell
me who's entering to my database, what's the user do and which changes he
made. Is like tracking every step the user is doing in my database. It was
one of my boss recommendations to increase security by having a tracking.
I
have the log in form, which is working great.
I'll appreciate if anyone let me know how to create a tracking or if it
needs
a program that can help me keep track of who's entering the database.
Also,
any suggestions too.
There is no built-in feature, you have to create it yourself but yes, you
can log users' names, computer name and times on and off by attaching code
to your main form's load and unload events or to a hidden form that opens
and closes with the database app. I track data changes in some of my apps
by calling a function from the forms' before update and delete events.
The type of coding is fairly complex so if you're new to coding it might be
a bit of a baptism of fire. This is the function I call for data changes,
it assumes you have various tables and queries in place which you should be
able to work out (example of calling code is Call libHistory(Me,
Me.txtIssueNo, "BeforeUpdate"):
Public Function libHistory(frmForm As Form, lngID As Long, strTrans As
String)
'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpdate & Delete events
Dim ctl As Control
Dim db As Database, rs As Recordset, strSQL As String, strUser As String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)
strUser = libUserName() 'This is another custom function not documented
in this thread
For Each ctl In frmForm
'Ignore controls such as labels
If ctl.Name Like "cmd*" Then GoTo Skip
If ctl.Name Like "btn*" Then GoTo Skip
If ctl.Name Like "txtXPID" Then GoTo Skip
If ctl.SpecialEffect = 0 Then GoTo Skip
If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
"ogr*" Or ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If ((IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
(IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
Or ctl.OldValue <> ctl.Value) Or strTrans = "Delete" Then
With rs
.AddNew
![DataSource] = frmForm.Name
![ID] = lngID
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue 'Don't record Old Value for
an appended comment
If strTrans = "Delete" Then 'Record the fact that the
record was deleted
![NewValue] = strTrans
Else
![NewValue] = ctl.Value
End If
![UpdatedBy] = strUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function
Good luck!
Keith.
www.keithwilby.com