Expert guide

See Who Is Logged Into an Access Database

Identify connected users, log sessions, and clear everyone out politely for maintenance

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: the ACE user roster, read with ADO OpenSchema in VBA, lists the machines currently connected to the back end. For names, have each front end write the Windows user, machine and time to a login log table when it opens and closes. To clear the database for maintenance, use a shutdown flag checked by a hidden timer form in every front end. We add these tools as part of Access VBA development.

"Who has the database open?" is the question every Access administrator asks before compacting, deploying a new front end or restoring a backup. Without tooling, the answer involves walking round the office or emailing everyone. A few small additions answer it in seconds and let you log people out without losing their work.

Method 1: Look at the Lock File

When anyone opens Ops_BE.accdb, Access creates Ops_BE.laccdb in the same folder and writes the computer name (and a default security name, usually Admin) for each connection. Opening it in Notepad shows a rough list of machines. However, entries for users who have left can remain until the last person closes the database, so treat it as a clue, not proof.

Method 2: The User Roster (Reliable)

Public Sub ShowUserRoster(ByVal backEndPath As String)
    Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
    Dim cn As Object, rs As Object
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & backEndPath
    Set rs = cn.OpenSchema(-1, , JET_SCHEMA_USERROSTER)   ' -1 = adSchemaProviderSpecific

    Debug.Print "Computer", "Login", "Connected", "Suspect"
    Do While Not rs.EOF
        Debug.Print Trim$(Replace(rs.Fields(0), Chr(0), "")), _
                    Trim$(Replace(rs.Fields(1), Chr(0), "")), _
                    rs.Fields(2), rs.Fields(3)
        rs.MoveNext
    Loop
    rs.Close: cn.Close
End Sub

The roster returns the computer name, login name (the Access security name, usually Admin), whether the connection is active, and a suspect state flag. Your own connection appears in the list too. Put the output in a list box on an admin form instead of the Immediate window for everyday use.

Method 3: A Login Log Table (Best for Names)

Back-end table tblSessions:
SessionID (AutoNumber), UserName, Machine, FrontEndVersion, OpenedAt, ClosedAt

' Startup form Open event
Private Sub Form_Open(Cancel As Integer)
    CurrentDb.Execute "INSERT INTO tblSessions (UserName, Machine, FrontEndVersion, OpenedAt) " & _
        "VALUES ('" & Environ("USERNAME") & "', '" & Environ("COMPUTERNAME") & "', '" & _
        AppVersion() & "', Now())", dbFailOnError
    TempVars.Add "SessionID", DMax("SessionID", "tblSessions", _
        "Machine='" & Environ("COMPUTERNAME") & "'")
End Sub

' Hidden form's Close event (runs when Access closes)
Private Sub Form_Close()
    On Error Resume Next
    CurrentDb.Execute "UPDATE tblSessions SET ClosedAt = Now() WHERE SessionID = " & _
                      TempVars("SessionID")
End Sub

Open sessions (ClosedAt is Null and recent) show who is in now. Sessions never closed point to crashes or PCs left on. Storing the front-end version also tells you who is still running an old copy.

Logging Everyone Out for Maintenance

Back-end table tblControl: ShutdownAt (Date/Time), Message (Text)

' Hidden form frmWatcher, TimerInterval = 60000 (one minute)
Private Sub Form_Timer()
    Dim t As Variant
    t = DLookup("ShutdownAt", "tblControl")
    If IsNull(t) Then Exit Sub
    If Now() >= t Then
        Application.Quit acQuitSaveNone
    ElseIf DateDiff("n", Now(), t) <= 5 Then
        Me.TimerInterval = 15000
        DoCmd.OpenForm "frmShutdownWarning"     ' non-modal: "Saving and closing in a few minutes"
    End If
End Sub

  • Set ShutdownAt to ten minutes from now with a message; every front end warns its user, then closes.

  • Your own admin front end skips the check so you stay in.

  • Block new logins while ShutdownAt is set: the startup form checks it and quits with the message.

  • Clear the flag when maintenance is done.

Use acQuitSaveNone for design changes but make sure forms save pending edits first; a form's BeforeUpdate validation can otherwise stop the quit. Test the process with a few users before relying on it.

When You Need It

TaskWhy everyone must be out
Compact and repair the back endRequires exclusive access
Change table design in the back endDesign changes need exclusive locks
Restore a backupReplacing the file while open corrupts sessions
Move the back end to a new serverLock file and links must be clean

These routines fit naturally into a split database with automatic front-end updates; see how to split an Access database. If users are frequently locked or kicked by errors rather than by you, read Access error 3218 and read-only databases.

FAQ

Who is logged in FAQ

Lock files, roster and shutdown

Access records machine names in the lock file (.laccdb or .ldb) next to the database. A VBA routine using the Jet/ACE user roster (ADO OpenSchema with the user roster GUID) lists computers currently connected. For user names, add your own login log table that the front end writes to on open and close.

You can open it in a text editor to see machine names, but entries can remain after users leave, so it is not reliable on its own. The user roster method reports which connections are actually active.

Build a shutdown flag: a small table in the back end with a 'please close' setting, and a hidden form in every front end with a timer that checks it every minute. When set, users get a warning and the front end closes after a countdown, allowing you to compact or update the back end.

Someone may still have it open, a front end on a PC that was left running, or a lock file is left from a crash. Use the user roster to identify the machine, and if no one is connected, remove the stale lock file once you are sure.

With SQL Server, use the server's own tools (sys.dm_exec_sessions, Activity Monitor) to see connections; the program name and host name show which Access front ends are connected. A login log table still helps you see Windows user names consistently.