Determine who has the frontend open? (1 Viewer)

[REZ]

I've got a multi user, frontend/backend database. I can see someone has left theirs open as their is a record locking file. Can I determine from the backend which computer has it open? or can I force close all frontends?

 

[Marshall Brooks]

Who's Logged On / Who's Connected

Description ... This utility will log the users who are connected to an MDB/E or ACCDB/E file via the UserRoster feature of the Jet/ACE database engine implemented with the introduction of Access 2000. In addition, the utility can manage the Connection Control (aka: Passive Shutdown) property...

www.access-programmers.co.uk

I use that - it will tell you what computer has the database open (including yours when you run it). Note that if you have a network environment (like Citrix), the computer name might not tell you what user has the FE open.

You can add code to the front end to run a hidden form with a timer to search for a specific file and close the database if that file exists (for example "Down_for_Maintenance.txt" in the BE folder, and log each user out after a specified amount of time, but you would have to update the FE to use that.

 

[Pat Hartman]

Not as easy as it should be to find out who is logged in. The .ldb or .laccdb assumes the users log in but that doesn't happen any more now that the built in security has been eliminated. So, what you see when you check the roster is "admin" for everyone. But you can see the computer name and that could help. I attached my copy of a form that does the display along with the help file. The link (in the help file) doesn't work so I attached my copy. I don't remember if I made any changes to the original. My apologies. I may have fixed it to read .accdb files. You need to click the down arrow to get the other search criteria when you press on the button to pick the database.

EDIT - I just noticed the combo on the third column. You can change it to Windows User which should give what you need.

I don't have code to log everyone out handy but if you search you can find it.

 

[The_Doc_Man]

You probably cannot force anyone's FE to close if the FE's host machine is still active. Windows inter-process (and network) security would bar you from affecting someone else's session forcefully.

A method I used for determining who was in the BE at the moment was that when a user launched my app, the startup form (it was a type of dispatcher or switchboard) queried Windows to find out from the domain who it was that was connecting. That startup code then put the date, time, and domain's username in a table along with an "IN" string. When persons used the app's QUIT function, the shutdown code put the date, time, domain's username, and "OUT" in the table. So all I had to do was look at the most recent IN/OUT records to see who had come in and hadn't left yet. Every so often, I flushed the table to remove all IN records older than the youngest OUT record for that user. Any time the DB went down successfully, I archived the table since no one could be in it during maintenance.

Later I added the feature that the startup form had a timer that checked a particular table. I would put an entry in the table for a future time and date and have a text message that said "Database going down for maintenance at 4 PM" (or whenever.) The timer would look in that maintenance schedule table for the nearest future schedule time. Users could see the scheduled dates and times for as far in the future as I had put in, but only the closest future time counted. Anyone still in the DB at the start of the scheduled down time would get a message "You have 10 minutes to exit from the DB." Then at the indicated time the app would start forcefully shutting itself down by finding forms to be forcefully closed.

This point is key. You can't shut down someone else's session through Windows commands... but there is no barrier at all to having a program shut itself down for each user that has it open at the time.

 

[MarkK]

Also, the lock file is just a text file, so you can take a copy, and open the copy in a text editor. Or you can write the contents to the immediate pane with code as simple as...

Private Sub PrintUsers()
    With CreateObject("Scripting.FileSystemObject")
        Debug.Print .OpenTextFile("C:\YourDb.laccdb", 1).ReadAll
    End With
End Sub

 

[Marshall Brooks]

Force Close Code:

I mainly used this:
https://www.databasejournal.com/fea...8586/Auto-Logout-Users-for-DB-Maintenance.htm

There were some errors in the code - mainly his countdown timer only updates every 10 seconds. I changed that so that it counts down each second, but I don't remember what I changed.

I think I added this line in frmLogoutStatus:

Private Sub Form_Open(iCancel As Integer)
   On Error GoTo Err_Handler
  
   mdat_StartCountdownTime = DateAdd("n", 3, Now())
   Me!txtMinsSecs = "3 minutes and 0 seconds"
   Me.TimerInterval = 1000
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.DESCRIPTION, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Also - in many cases some of his descriptions in the website text are inaccurate and in many cases they do not match what he does with his sample database.

He has it set to check every ten seconds, I changed it to check every 60 seconds. Then you get a pop-up warning that the database will close in 3 minutes with a count-down. If you close the pop-up, it reappears at 2-minutes left, 1-minute left, and 20 seconds left, then it shuts down the FE.

He has it set to read a value in a table. I changed that to look for a particular file in the BE folder per this:
https://docs.microsoft.com/en-us/office/troubleshoot/access/shut-down-custom-application-remotely

The reason is that often if I need to force close the database, it is due to some network error, in which case, I can't get into the database to set the flag on the table - but might not matter - see below:

Some other considerations:

• It was recommended to have the flag set for the text file existing. You could just as easily set the flag to shut down the database if the flag file was NOT present, but then you have a greater risk of the file accidentally being deleted and everyone being locked out of your database.
• I made a modified version of FrmLogoutStatus called FrmDownForMaintenance - Essentially, it is the same form but without the timer code and the text just says "Database is down for maintenance. Please try again later." My startup code checks for the flag file and if present, this form is shown and clicking okay or close on the form shuts down the database. The first link code ignores this, so someone could have had the database closed and then logged in after you set the shutdown code, and they can be working in the database for up to 4 minutes before they are kicked out again.
• The code may not work if the database is in an inconsistent state error - which is usually when you need to kick everyone out.
• The code does not work if the database has a modal pop-up open. I typically use custom forms for Enhanced Message Box and InputBoxes and I run this code before shutdown to close any open user forms:

Sub UnloadAllForms(Optional dummyVariable As Byte)' https://stackoverflow.com/questions/48562941/closing-any-open-userform
'Unloads all open user forms
Dim I As Long
For I = VBA.UserForms.Count - 1 To 0 Step -1
Unload VBA.UserForms(I)
Next
End Sub

Called from the frmLogoutStatus timer event as follows:
If intIMins <= 0 And intISecs <= 0 Then
If LCase(CurrentProject.Path) = "c:\users\<my user name>\desktop" Or LCase(CurrentProject.Path) = "<Database development folder>" Then
' Bypass the shutdown if the database is on MB's Desktop so that he can do maintenance on the database.
Else
Call UnloadAllForms
DoCmd.Quit acQuitSaveAll
End If
Else
On Error Resume Next
DoEvents
Me.txtMinsSecs = "" & intIMins & " minutes and " & intISecs & " seconds "
Me.Repaint
End If

The blue text ensures that I can tell that the logout message was generated, but I won't be kicked out if I have to do maintenance from the front end. However, keep in mind that if a user has an error message pop-up displayed or a standard MsgBox or InputBox displayed, and is away from their computer, they won't be logged out.

 

[Marshall Brooks]

I don't remember if I made any changes to the original. My apologies. I may have fixed it to read .accdb files. You need to click the down arrow to get the other search criteria when you press on the button to pick the database.

EDIT - I just noticed the combo on the third column. You can change it to Windows User which should give what you need.

If it is the same project that I linked to - and I think it is. Then it works with .accdb files. It works for me and I know I didn't make changes to it. Also - if the database is in an inconsistent state, WhoIsConnected won't be able to get any info to show you.

The third column might or might not help. On a typical network, it gives you the user name, but not the person's name - so if you don't know user name to user, it doesn't help - but neither does computer name.

If your company uses Citrix or similar, the second column will show the server and the third column will show "Unable to acquire." In this case, you would need to talk to the Citrix network admins and they can probably tell you who has the file open.

 

[The_Doc_Man]

Just as a clarification to my earlier post, I was perhaps a LITTLE bit vague in the way I said something...

In the absence of admin-level rights, it is illegal to intervene in the status of another process. As an administrator on a shared machine, you could shut down other user processes easily enough. Unless you are a domain-level admin OR a local-machine admin on the shared system, Windows inter-process security stands in the way. But if you can persuade the app to shut itself down, then you have not violated system security. Which is why so many folks are responding with ways to make the app shut itself down.

 

[Marshall Brooks]

Just as a clarification to my earlier post, I was perhaps a LITTLE bit vague in the way I said something...

I appreciate the clarification. I read reply #4 and was initially thinking "My shutdown code only works for ME and not my other users?" (Not sure I would know otherwise - but then I read the last sentence and understood - but clarification might help for the OP.

Also, as an aside - it is also possible to shut the database down after a certain period of inactivity. I don't use that on my database, so I don't know the exact code, but it can be done and can be useful.

 

[The_Doc_Man]

Also, as an aside - it is also possible to shut the database down after a certain period of inactivity

Again, when it is done by the app ON ITS OWN (other than Access properties and settings) and not due to external mandates, not a violation. Sort of like that vacation tag line "What happens in Las Vegas STAYS in Las Vegas."