Module Help Updating Tables

[CCheatham]

I want to take the following module, and have the info added to a
table when the user opens the database, and removes the user from the
table when it's closed. I have to maintain this database and need to
be able to see who's in it at the time. It's practically used 23 hours
a day. Any suggestions? Thanks.

------------------------------------------------------------
Function fGetUserName() As String
' Returns the network login name
Dim lngLen As Long, lngRet As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngRet = apiGetUserName(strUserName, lngLen)
If lngRet Then
fGetUserName = Left$(strUserName, lngLen - 1)
End If
End Function

 

[Guest]

Do this in a number of my applications too.

I usually have a Splash screen for my applications, that I use to perform
critical tasks like this. This splash screen loads as the startup form and
in the Timer event (I want the form to display before the code runs) I do
something like the following.

Private Sub Form_Timer()

me.timerinterval = 0 'turns the timer off so it doesn't fire again
currentdb.execute "UPDATE tblUsers SET On_At = NOW() " _
& "WHERE User_ID = '" & fGetUserName & "'"

End sub

Then, I have a close button on this form which I use to set the On_At field
of tblUsers to NULL when the user logs off.

This method is not fool proof. The down side is that if the users computer
locks up, or they don't exit by hitting the close button, the On_At field
never gets set to NULL. so you think the user is still on.

You could do something like modify the Timer event of that splash screen so
that every 5 minutes it updates another field (Still_On_At ) with the current
time. then, you would be able to identify anyone where the Still_On_At field
is not null and where the time is within 6 minutes of the current time. If
the time was greater than 6 minutes, you would know that they had not logged
off properly.

You might also want to institute a mechanism to force users off if you have
set a flag in your application. I've got a couple of applications that are
used by a large number of people. When I need to perform maintenance on the
database, I set a field (InMaintenance) in my tbl_Application_Parameters to
True. The timer even of the splash screen checks when the form is loaded to
determine whether the flag is true. If so, it displays a message to the user
that the application is in maintenance and requests that they try again
later. The Timer event of the splash screen also checks every 5 minutes to
see whether this field is true, and if so, displays a warning message to the
users and starts a countdown timer, usually set at 10 or 15 minutes. When
the countdown timer reaches zero, it executes a series of steps to close any
forms that might be open. This procedure took some getting used to by my
users because if they had the application open and had an unsaved record open
when the program began the shutdown sequence they lost their work, that was
why we settled on a 10 to 15 minute warning.

HTH
Dale

 

[Guest]

BTW,

If you use this technique, You need to first test to see whether the userID
is in your tbl_Users table. If not, then add a record to that table.

Private Sub Form_Timer()

Dim strUserID as string
Dim strCriteria as string

me.timerinterval = 0 'turns the timer off so it doesn't fire again

'Check to see whether the user is in tblUsers. If not, add record
strUserID = fGetUserName
strCriteria = "[User_ID] = '" & strUserID & "'"
IF DCOUNT("User_ID", "tblUsers", strCriteria) = 0 then
Currentdb.execute "INSERT INTO tblUsers (User_ID) " _
& "VALUES ('" & strUserID & "')"
endif

currentdb.execute "UPDATE tblUsers SET On_At = NOW() " _
& "WHERE User_ID = '" & strUserID & "'"

End sub