Logging User Access

[Guest]

Access 2002.
Basic Access knowledge / Very little VBA experience

I have a front end/back end db with user level security. The database was
set up for individuals to view statistics (i.e. they do not add or edit data
- just view it). I feel that this database is not being used and I want some
data to support that.
I want to log when users 'sign in' or 'log' into the db. Most of the post's
I've read center around actions taken by the user (insert/edit/delete) and
are pretty advanced for my skill level at this time. I simply want to track
who is using the database and how often.
Any assistance would be greatly appreciated. Thanks in advance for your
patience - just trying to start small and build up to more advanced
auditing/tracking.

 

[Keith Wilby]

Access 2002.
Basic Access knowledge / Very little VBA experience

I have a front end/back end db with user level security. The database was
set up for individuals to view statistics (i.e. they do not add or edit
data
- just view it). I feel that this database is not being used and I want
some
data to support that.
I want to log when users 'sign in' or 'log' into the db. Most of the
post's
I've read center around actions taken by the user (insert/edit/delete) and
are pretty advanced for my skill level at this time. I simply want to
track
who is using the database and how often.
Any assistance would be greatly appreciated. Thanks in advance for your
patience - just trying to start small and build up to more advanced
auditing/tracking.

The easiest way IMO is to record the CurrentUser property in a table along
with a timestamp using your main form's Open/Close events. You can also
capture network details (username, computer name etc) unsing the "Environ"
property (search the help) although some developers frown upon it since
someone with a bit of know-how can manipulate environmental variables -
you'd need to assess the risk to your system on that one, ie, how likely
anyone would want to go to that trouble.

HTH - Keith.
www.keithwilby.com

 

[Guest]

Thanks. I checked again in the discussion groups but did not see an example
of how to do this. If it is not too much trouble, could I get more detail
(sample code?) on recording the CurrentUser property in a table along with a
timestamp using my main form's Open/Close events?
Thanks in advance

 

[Keith Wilby]

Thanks. I checked again in the discussion groups but did not see an
example
of how to do this. If it is not too much trouble, could I get more detail
(sample code?) on recording the CurrentUser property in a table along with
a
timestamp using my main form's Open/Close events?
Thanks in advance

Assumptions:

You are using DAO.
You have a table called tblLog with fields fldCurrentUser (text), LogIn
(date/time), LogOut (date/time), UserName (text), computername (text). This
table has an autonumber field called LogID.
You have a query called qtblLog which is based upon table tblLog.
You have a global variable called glngLogID (long type) to contain the ID of
the current log record for log out purposes.

In your main form's Open event:

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
!UserName = Environ("USERNAME")
!fldCurrentUser = CurrentUser
!computername = Environ("COMPUTERNAME")
!LogIn = Now()
.Update
.Bookmark = .LastModified
glngLogID = !logid
.Close
End With
Set rs = Nothing
Set db = Nothing

In your main form's Unload event:

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

HTH - Keith.
www.keithwilby.com