Logging users in access.

sampow · December 10th, 2003, 11:21 AM

When a user access a database, a record locking file is created, when opened with notepad it tells you exactly who is using the database.

From knowing this, is there any way of logging these users into another text file along with the date of when they accessed the database. This would be very usefull as i could then monitor how many people accessed the database in a day and exactly who the users were.

If any one knows of a method of doing this please let me know.

Thanks,

Sam Powell
Information Systems Officer
Neath Port Talbot College

Steven · December 10th, 2003, 09:17 PM

What you could do, is have a function that gets fired on open (whether it be through an autoexec macro or otherwise) that writes to a text file - for example

Code:

Function PutThey()
Dim strMsg as String
strMsg = CurrentUser & " - " & Now
Open "\\Server\Share\Directory\File.txt" For Append As #1
Print #1, strMsg
Close #1
End Function

Of course, there are other ways (not to mention better ways of writing the above code), such as using a table in your database, and appending a record to it, such as:

Code:

DoCmd.RunSQL "INSERT INTO tbl ( [User], Dt, [Db] )" _
    & " Values ('" & CurrentUser() & "', Now(), 'Something');"

Steven

I am a loud man with a very large hat. This means I am in charge

sampow · December 15th, 2003, 07:17 AM

Quote:

quote:Originally posted by Steven
What you could do, is have a function that gets fired on open (whether it be through an autoexec macro or otherwise) that writes to a text file - for example

Code:

Function PutThey()
Dim strMsg as String
strMsg = CurrentUser & " - " & Now
Open "\\Server\Share\Directory\File.txt" For Append As #1
Print #1, strMsg
Close #1
End Function

Of course, there are other ways (not to mention better ways of writing the above code), such as using a table in your database, and appending a record to it, such as:

Code:

DoCmd.RunSQL "INSERT INTO tbl ( [User], Dt, [Db] )" _
    & " Values ('" & CurrentUser() & "', Now(), 'Something');"

Steven

I am a loud man with a very large hat. This means I am in charge

That works fine thanx, but the only problem is the command CurrentUser only returns the user group which they belong to e.g. Admin. Is there a way to actually return the user name of the person accessing the databse? Thank you Steven

Sam Powell
Information Systems Officer
Neath Port Talbot College

jemacc · December 15th, 2003, 12:49 PM

I think replacing it with (suser_sname()) should return the login for the domain user.

Steven · December 15th, 2003, 08:46 PM

I see you don't have user level security on your database - that's a slightly different story then.

To get the network user name - you could use this function:
http://www.mvps.org/access/api/api0008.htm
and just replace CurrentUser with fOSUserName
Another possibility is to use the Environ function - this way you can also get the computer name (among other things), if, for some reason you want it.

Steven

I am a loud man with a very large hat. This means I am in charge

sampow · December 16th, 2003, 06:50 AM

Quote:

quote:Originally posted by Steven
I see you don't have user level security on your database - that's a slightly different story then.

To get the network user name - you could use this function:
http://www.mvps.org/access/api/api0008.htm
and just replace CurrentUser with fOSUserName
Another possibility is to use the Environ function - this way you can also get the computer name (among other things), if, for some reason you want it.

Steven

I am a loud man with a very large hat. This means I am in charge

Thank you very much Steven you have been most helpfull. If you have a link or any information about the Environ function i would be very interested to learn more about that! :)

Sam Powell
Information Systems Officer
Neath Port Talbot College

Steven · December 16th, 2003, 07:50 PM

You can look up the Environ function in VBA help, (just type in Environ in the Help Search),
but here's some code that should help you anyway - it will print all the environment variables to the immediate window (It's just a slight change from the example in the help file):

Code:

Function ShowEnviron()
Dim Indx As Integer
Indx = 1
Do
    Debug.Print Environ(Indx)
    Indx = Indx + 1
Loop Until Environ(Indx) = ""
End Function

Steven

I am a loud man with a very large hat. This means I am in charge