MS Access Maintaining a log of users who log in and what time they log in.

I put user level security on my database.

How can I maintain or review of log of who logged in and what time they logged into the database?
AHAXtremeAsked:
Who is Participating?
 
Rick_RickardsConnect With a Mentor Commented:
1) Create a table named tblUserLog with fields as follows...

UserLogID (AutoNumber) Primary Key
User (Text Field 64 Characters)
DateTime (Date/Time Field) Default value = Now().

2) Run the following code with an AutoExec Macro or any other method that assures the code is executed when the user opens the database...

      CurrentDb.Execute "INSERT INTO tblUserLog ([User]) VALUES ('" & Replace(CurrentUser(), "'", "''") & "')"

Since the DateTime field defaults to Now() it will automatically log the time every time a new record is added with the query above.
0
 
UniqueDataCommented:
Just about every office as different logins for each user.  I have run across a few that use the generic Admin login on all their computers.  If you are in a case like this then you can not use CurrentUser.  You would have to create a login screen and use the textbox they type their name instead of CurrentUser in Ricks answer.
0
 
AHAXtremeAuthor Commented:
I put in my autoexec macro a runcode option with your code CurrentDb.Execute "INSERT INTO tblUserLog ([User]) VALUES ('" & Replace(CurrentUser(), "'", "''") & "')"

And I get the following error.

Microsoft Office Access can't find the name CurrentDb you entered in the expression.

Please advise.
0
 
AHAXtremeAuthor Commented:
Thanks
0
 
Rick_RickardsCommented:
Glad you found the answer you were looking for.  

If you wanted to run the code...

 CurrentDb.Execute "INSERT INTO tblUserLog ([User]) VALUES ('" & Replace(CurrentUser(), "'", "''") & "')"

In a macro it would have to read...

=CurrentDb.Execute("INSERT INTO tblUserLog ([User]) VALUES ('" & Replace(CurrentUser(), "'", "''") & "')")

This however is not desireable as it will likely log the entry twice.  A better method is to move the code into it's own function such as the code snippet below then within the macro set it to runcode and name the function as follows

=LogUser()


Function LogUser()
     CurrentDb.Execute "INSERT INTO tblUserLog ([User]) VALUES ('" & Replace(CurrentUser(), "'", "''") & "')"
End Function

Open in new window

0
All Courses

From novice to tech pro — start learning today.