Can I trace the last person who used the database?

[nicolenwn]

Hi all,

Great day(:

I have a database stored in the shared drive and currently the records are being changed mysteriously. Is there a function in MS Access that allows you to check who was the last person who used the database or who was the one who changed a record?

Many thanks for all the help received(:

Cheers,
Nicole

[ADezii]

Assuming you have User-Level Security defined, you can Log each User's opening and closing of the Database by writing to a Hidden Table, then easily retriving that information. You can use something similar to:

Dim strSQL As String
 
strSQL = "INSERT INTO tblLastUser ([UserName], [UsageTime]) VALUES (CurrentUser(), Now())"
 
CurrentDb.Execute strSQL, dbFailOnError

To retrieve the Last User's Name and Time Logged on:

MsgBox "Last User: " & DLast("[UserName]", "tblLastUser") & vbNewLine & _
       "Last Access: " & DLast("[UsageTime]", "tblLastUser")

Example:

Last User: Admin
Last Access: 2/11/2010 9:20:29 AM

[nicolenwn]

Hi ADezii,

By saying" Assuming you have User-Level Security defined, you can Log each User's opening and closing of the Database by writing to a Hidden Table"

Could you elaborate more about the User-Level Security as i have no idea what that is about. And also with regards to the Hidden Table,does this mean i have to create a new table?

Sorry for the many question, this is all very new to me. Where will i have to put the codes? Do i have to put it in each table and is there a button i can cliick to retrieve the info for the last person using the DB.

Thanks in advance for all your help ADezii, appreciate it heaps(:
Cheers,
Nicole

[NeoPa]

User-Level is a bit of a dead duck now. I believe they don't even support it going forward from A2007.

However, this does open my eyes a little (Thanks ADezii). I was assuming, quite sleepily, that you were asking for a general concept of how to determine such usage (IE. for any unmodified database). For a particular database you could always log the user accesses. In place of the Access level user though (almost all Access users simply use Administrator for all purposes) you could use the network user, or simple account name if the user is not networked.

Function to Return UserName (NT Login) of Current User and Retrieve User ID will help to get those details. ADezii's code and explanation gives the basic idea of how to go about this.

[NeoPa]

@nicolenwn
Yes it would. You could hide it from the users as ADezii has suggested if that helps, but you'd need a table to log the access times into.

It could be done by logging to an outside file, but that would be appreciably more complicated and somewhat less logical.