Access Cookbook

Track Which Users Have a Shared Database Open

Problem

You need better control over a networked Access application. Is there any way you can track which users are logged in and which machines they are using?

Solution

Access tracks this information in the .LDB file, but that file sometimes lists users who have already logged out, so you can’t just open it in Notepad and take a look. This solution opens a special ADO recordset that shows you exactly the information you need. The sample form lists user and machine names in a list box.

Import frmCurrentConnections (see Figure 10-23), which shows which users are logged into any shared database. Note that if you are using a split architecture, the shared database is the one that contains your tables. Open the VBA Editor and use the Tools → References dialog to ensure that you have a reference to Microsoft ActiveX Data Objects, Version 2.1 or later.

Figure 10-23. frmCurrentConnections shows which users are logged in

You can open the form at any time to see who’s logged into the database. If you want to keep the form open, you can click the Refresh button to update the display. If you have not implemented security, all users will appear as Admin, but you will see their individual machine names, as in Figure 10-23.

Discussion

The key to this solution is the use of a very peculiar kind of ADO recordset that retrieves metadata from the ...