Tracking Logon and Logoff Events

[applebite]

I'm a trainer trying to demo a function in my application. I have an audit table in my Access database with cols for date, start time, end time, event, username. I'm using a query tool that will send a SQL statement to the audit table when the user logs on and logs off.

insert into myaudit(Datets, Startts, Event, Username) values (DATE(),TIME(),'LogOn','Jane')

All works well except there is row for the log on event and a separate row for the log off event, and I haven't figured out how to capture the username (right now I have it hardcoded in the SQL.

I want to use an update statement to get the log off event on the same row as the users log on, but can't figure out what to use in the where clause. Any suggestions.

Thanks in advance.

 

[bholm]

When designing a db table it's a good idea to always have a column that contains unique data for this purpose. I would suggest you create a column for an ID value and sequencially increment it with each login record. Then you can update any login with the logout info by using "WHERE ID=x" in your UPDATE query where x is the specific id of that entry. I don't see any other currently existing columns you can use. THe user is not since they may log in and out several times a day. Combining several columns is another option sometimes but in your case that doesn't help since the same user could log in during the day. You could use user and date and time I suppose but that would be tricky.

 

[JoeMiller]

You can use this function to get the current user (if you're using Access security):

CurrentUser()

If you're not, you can get the computer's logged in username (if you're on an NT based system):

Environ("UserName&quot

Or you can use this function to get the computer's logged in user name (place this in a module):
[tt]
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
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = &quot;&quot;
End If
End Function
[/tt]

Then call it like this:

fOSUserName()

I agree with BHolm that you need to add a key to your table, even if it's an autonumber. As far as capturing log on and log off, I assume you have a switchboard like form that stays open while the user is in the db, you can use that forms OnOpen and OnClose events to determine LogOn and LogOff, then just place the appropriate text.

HTH


Joe Miller
joe.miller@flotech.net