Log database users

spacepro

Registered User.
Local time
Today, 19:10
Joined
Jan 13, 2003
Messages
715
Hi Everyone,

Is there a simple way of logging users when they log in/out of a database??

Currently I have a secure database, but I might change this.

I have searched the forum, but I have been unable to find a answer.

I have tryed fabolou database launcher, but this does not access secure databases and it's send message function does not work on the work's server.

Thanks
Andy
 
Yes it is.

First create a table with these fields; Call it LogInTable

UserName
LogInDate
LogInTime
LogOutTime

Then copy this code and paste it into a new module exactly as it is.

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String

' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String, db As Database, r As Recordset, rst As Recordset
Dim table2 As String
Set db = CurrentDb
Set r = db.OpenRecordset("LogInTable")



strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If

r.MoveLast
r.AddNew
r.Fields("UserName") = UCase(fOSUserName)
r.Fields("LogInDate") = Date
r.Fields("LogInTime") = Time
r.Update


End Function

Then OnLoad of the switchboard - Call FosUserName()

The data will then be posted to the table when the user logs in.

OnClose of the switchboard run an UpDate query to add the logout time.

If you do a Me.refresh on the timer of the switchboard - say every 30 seconds, you'll pick up new users who have logged on.

Col
:cool:
 
Hi Col,
Thanks again
I seem to be going round in circles!

I will create the table and the module, but I am abit confused on the latter.
Been at it all day. My apologies.

Any chance of a bit more insight to calling the module.

Many Thanks
Andy
 
Andy

I presume you mean this bit-

Then OnLoad of the switchboard - Call FosUserName()

Assuming you have a switchboard, go to properties and OnLoad, select EventProcedure and type in Call FosUserName()

Col
:cool:
 
As an added little bonus, I'd suggest making a hidden form with the following code on it's Unload() event.

Code:
Private Sub Form_Unload(Cancel As Integer)
    If Not pboolCloseAccess Then Cancel = True
End Sub

And in a module:

Code:
Public pboolCloseAccess As Boolean

Function Main()
   pboolCloseAccess = False
   DoCmd.OpenForm "frmHidden", acHidden
   DoCmd.OpenForm "frmYourSwitchboard", acNormal
End Main

with your autoexec macro running the code Main()

This way you'll disable the X button in the top-right so that you prevent them closing the database without the Quit button which would mean they would still have been registered within the table although they would have left the database.

Closing with Task manager would still cause problems...
 
Hi Col,

Thanks for that, and yes I do have a switchboard.
What I was doing was viewing switchboard in design view then right clicking the swsitchboard instead of selecting properties from the top menus - because I couldn't find the onload and unload commands.

Found them though!!
I am just a bit unsure on how to set the timer . Any clues?

Just like that quote "Build a better system and get a better idiot"
That's me!

Thanks
andy
 
Its interesting you say that Mile-O because I've found that the update query to record the LogOut time does work when you use the X button. I was all ready to put in similar code but didn't need to.

I would have thought it wouldn't - maybe I've got a special version of A97:D

I guess on reflection though its better to have it in there.

Thanks for the input though

Col
:cool:
 
Thanks Mile,
I will try that as well!
I like little bonuses!

Thanks
Andy
 
Andy

In the switchboard OnTimer event type in Me.Refresh - set the timer to 30000

Col
:cool:
 
ColinEssex said:
I would have thought it wouldn't - maybe I've got a special version of A97

Or maybe you've got more intelligent users...
 
Col & Mile-O

Many thanks for your input.

I will post back if I have any problems.

Andy;)
 
Yeah - nice one Mile-O - I wish:D

I have found that if you give them a good slapping when they do it wrong they do learn eventually. (and threaten to take away their database and supply them with pen and paper)

Col
:cool:
 
Pen and paper's too good for them; I threaten with crayons and papyrus.
 
Hey Guys,
Just a thought!

Is there anyway through VB Code or other means of sending a message to other users over a network, say to log off, but only send a message to the users that are logged on?

Or does that pen/paper or crayons have to come out.

Andy
 
There may be something on that on the forums - have a search. I'm not sure if I've got anything on it - I don't think I have I'll check - but it'll have to be monday now.

Col
:cool:
 
Mile-O-Phile,

Thats cool! The only prblem it doesnt let me use the docmd.close for the form. I had to ctrl-alt-del to get out of it. I like it though!
 
Whoops! Forgot to say that on the close button on the switchboard you would have to put an extra line of code being:

pboolCloseAccess = True
 
you just get too excited about posting new code:p
 
Hi Colin/Mile

I tried the code you supplied very kindly, but the

db as Database part brings the debugger up and I tried to make it work, but it doesn't.

I am using office xp.
Am i doing something wrong, any chance of an example database??

Many thanks
Andy
 
Getting user ID?

What about using Environ("username") to get the user's name??
 

Users who are viewing this thread

Back
Top Bottom