By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Can I trace the last person who used the database?

P: 23
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
Feb 11 '10 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "INSERT INTO tblLastUser ([UserName], [UsageTime]) VALUES (CurrentUser(), Now())"
  4.  
  5. CurrentDb.Execute strSQL, dbFailOnError
To retrieve the Last User's Name and Time Logged on:
Expand|Select|Wrap|Line Numbers
  1. MsgBox "Last User: " & DLast("[UserName]", "tblLastUser") & vbNewLine & _
  2.        "Last Access: " & DLast("[UsageTime]", "tblLastUser")
Example:
Expand|Select|Wrap|Line Numbers
  1. Last User: Admin
  2. Last Access: 2/11/2010 9:20:29 AM

Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,615
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "INSERT INTO tblLastUser ([UserName], [UsageTime]) VALUES (CurrentUser(), Now())"
  4.  
  5. CurrentDb.Execute strSQL, dbFailOnError
To retrieve the Last User's Name and Time Logged on:
Expand|Select|Wrap|Line Numbers
  1. MsgBox "Last User: " & DLast("[UserName]", "tblLastUser") & vbNewLine & _
  2.        "Last Access: " & DLast("[UsageTime]", "tblLastUser")
Example:
Expand|Select|Wrap|Line Numbers
  1. Last User: Admin
  2. Last Access: 2/11/2010 9:20:29 AM
Feb 11 '10 #2

P: 23
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
Feb 12 '10 #3

NeoPa
Expert Mod 15k+
P: 31,299
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.
Feb 12 '10 #4

NeoPa
Expert Mod 15k+
P: 31,299
@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.
Feb 12 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.