Currently logged-in users (1 Viewer)

stevekos07

Registered User.
Local time
Today, 08:38
Joined
Jul 26, 2015
Messages
174
I am trying to set up a table that will add and delete users as they log in and out on Access. I have a login form that looks up the username and password etc. but I need to know who is logged in at any time so that if I need to conduct any maintenance in the back end I can communicate with users directly rather than issuing general notices over and over.

Is there a tutorial somewhere I can check out, or can someone here help me with this?

I have set up a table tblLoggedOnUsers, but I'm not sure what to do from there. I am thinking that setting a temp variable and referencing the table would be the way to go but I'm not sure how to do this. I'm not very experienced with using TV's.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:38
Joined
Feb 19, 2013
Messages
16,555
look to the bottom of this thread - plenty of examples
 

sneuberg

AWF VIP
Local time
Today, 08:38
Joined
Oct 17, 2014
Messages
3,506
@CJ The URL to the thread didn't stick.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:38
Joined
Feb 19, 2013
Messages
16,555
I meant the 'Similar Threads' block right at the bottom of the page:)
 

sneuberg

AWF VIP
Local time
Today, 08:38
Joined
Oct 17, 2014
Messages
3,506
I have attached some files that might help you out. The LogInUserTempVarTest database demonstrates how easy it is to use tempvars for something like you want to do. In the code below I set a TempVar named UserName to the value of Environ("UserName"). If your users log into a network so that Environ("UserName") is unique you might consider using this rather than have your users log in to Access. The code creates a record in tblCurrentUsers if there is not already one there. Otherwise it updates an existing record for the user. Of course in a real situation tblCurrentUsers would be in the backend.


Code:
Private Sub login_Click()

TempVars!UserName = Environ("UserName")

If DCount("*", "[tblCurrentUsers]", "[UserName] = '" & TempVars!UserName & "'") = 0 Then
    CurrentDb.Execute "INSERT INTO tblCurrentUsers (UserName, LoggedIn, LastTimeIn) VALUES('" & TempVars!UserName & "'," & True & ",#" & Now() & "#)", dbFailOnError
Else
    CurrentDb.Execute "Update tblCurrentUsers SET LoggedIn = True, LastTimeIn = #" & Now() & "# WHERE UserName = '" & TempVars!UserName & "'", dbFailOnError
End If

End Sub
The tempvar lives for as long as the Access application is open so I use it to log off users in the code below. This happens to be in the Form Unload event of the only form in this database. You would want to put code like this in the Form Unload event of some form that stays open the whole time the Access application is open. If you don't have a form like that then I suggest created one and just open and hide it when the application starts. Having the code in the Form Unload event has the advantage that this fires even if the user closes the application with the Access close button.

Code:
Private Sub Form_Unload(Cancel As Integer)

CurrentDb.Execute "Update tblCurrentUsers SET LoggedIn = False, LastTimeOut = #" & Now() & "# WHERE UserName = '" & TempVars!UserName & "'", dbFailOnError

End Sub



The ADO Show User Roster Backend zip file contains some code that apparently extracts user information from the lock file. I don't know exactly how it works. It was posted on this forum a while back by I think arnelgp. To see this work you will have to use the link manager to link the table in the frontend to the backend. Maybe you could use this at least as a supplement to the other method.
 

Attachments

  • LogInUsersTempVarTest.accdb
    512 KB · Views: 150
  • ADO Show User Roster Backend.zip
    41.2 KB · Views: 172

static

Registered User.
Local time
Today, 15:38
Joined
Nov 2, 2015
Messages
823
Log which users use what PC then read the lock file.
It won't be 100% accurate but neither will any login system you create yourself.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:38
Joined
Feb 28, 2001
Messages
27,005
Static is correct but note that even the lock file is often wrong. The problem is that if a user takes a "ragged" exit, the events that you could use to note that user's departure don't fire correctly sometimes. I'm not gonna say I'm perfect 'cause I know that to be a blatant lie, but I'm pretty thorough and understand events pretty well. I have NEVER been able to properly maintain an in-use count using either my own methods or looking at the lock table.

The REAL method involves being able look into the back-end server's network connection table - and THAT is a high-privilege operation that probably wouldn't fly with your network security wonks.
 

static

Registered User.
Local time
Today, 15:38
Joined
Nov 2, 2015
Messages
823
The lock file isn't wrong, it just isn't updated very often.

If you can delete the lock file you know nobody has the database open. If you can't, at least one of the machines shown in the lock file has it open, you just don't necessarily know which one(s).
 

sneuberg

AWF VIP
Local time
Today, 08:38
Joined
Oct 17, 2014
Messages
3,506
In the end the OP wants to make sure no one is using the backend while he doing maintenance on it. I think if the OP tracks the users who log on and off and uses that in conjunction with the lock file information he would have enough information to attempt to open the backend exclusively for the maintenance. I think he should open the backend exclusively in order to ensure no one can log on during the maintenance. If he can't open the backend exclusively then he knows the information he had is wrong and will just have to pursue other means. Trying to delete the lock file sound like one of them.
 

Users who are viewing this thread

Top Bottom