create log table and audit

[Joe via AccessMonster.com]

I have a database that I want to log when a user log's on and off. I want the
user's name and date and time to be submitted to a table. I also want to be
able to create an audit table that add's the user name and what was changed
into a table and append to the table.

Your help is greatly appreciated.

 

[Anthos]

I have a database that I want to log when a user log's on and off. I wantthe
user's name and date and time to be submitted to a table. I also want to be
able to create an audit table that add's the user name and what was changed
into a table and append to the table.

Your help is greatly appreciated.

Access doesn't provide the ability to monitor a table and watch for
changes, this however can be done in SQL if you use that as your
backend.

Alterntively, you can setup on On Dirty Events for your form that save
Field.OldValue and Field.Text (or field.value) to a table so you can
record who did what change.
Outside of that, use a login form so that user's cant use the database
without it, and finally, you can record the user name and Now() to a
tbale that will be a nice audit trail for you.

(Keep in mind that if someone get's to your tables, they can delete
that audit trail too)

 

[Keith Wilby]

I have a database that I want to log when a user log's on and off. I want
the
user's name and date and time to be submitted to a table. I also want to
be
able to create an audit table that add's the user name and what was
changed
into a table and append to the table.

Since you're talking about users logging on I'm going to assume you've
implemented user level security.

You can call the following functions from the primary from's Open/Close
events to record the contents of the CurrentUser and Now functions:

Public Sub LogIn()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qtblLog", , dbAppendOnly)

With rs
.AddNew
!fldCurrentUser = CurrentUser
!LogIn = Now()
.Update
.Bookmark = .LastModified
glngLogID = !logid
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub

Public Sub LogOut()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qtblLog")

With rs
.FindFirst "LogID = " & glngLogID
If Not .NoMatch Then
.Edit
!LogOut = Now()
.Update
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub

This is the code I use to record user changes. It's a bit rough around the
edges but it does work and needs to be called from every event you want to
trigger it (ie Add New, Update, Delete). It requires the calling Form
object, the record's unique ID and a string representing the transaction
type to be passed to it:

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 DAO.Database, rs As Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)

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
'MsgBox ctl.name
'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] = CurrentUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Function

HTH - Keith.
www.keithwilby.com