Recipe 10.8 Track Which Users Have a Shared Database Open

10.8.1 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?

10.8.2 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.

10.8.3 Discussion

The key to this solution is the use of a very peculiar kind of ADO recordset that retrieves metadata from the Jet database engine. This metadata, also called schema information, is not data that you store in your tables, but data stored by the database engine—in this case, data about logged-in users, which is stored in the .LDB file. Here is the procedure that populates the list box:

Private Sub ListConnections( )
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strComputerName As String
    
    Set cnn = CurrentProject.Connection
    Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _
     "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    lboConnections.RowSource = vbNullString
    lboConnections.AddItem "Computer Name;Login Name"
    
    Do While Not rst.EOF
        If rst("Connected") Then
            strComputerName = rst("Computer_Name")
            lboConnections.AddItem _
             Left(strComputerName, _
             InStr(strComputerName, vbNullChar) - 1) & _
             ";" & rst("Login_Name")
        End If
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

After using that magic GUID value in curly braces to open the recordset, the code clears out the list box by setting its row source to an empty string. This allows the procedure to be called repeatedly to refresh the list as users come and go:

lboConnections.RowSource = vbNullString

The code then fills in the first row of data, which will become column headings because the list box ColumnHeads property is set to Yes. The ListConnections procedure uses a method of the list box that is new in Access 2002: AddItem. This method makes it a little easier to work with combo or list boxes that have a RowSourceType of Value List. You can populate such combo and list boxes by using a list of items delimited by semicolons or commas. Because this list box has two columns (the ColumnCount property is set to 2), the code must insert the data for both columns each time it calls AddItem. This is done by placing a semicolon between the columns:

lboConnections.AddItem "Computer Name;Login Name"

The fields of this recordset contain data terminated by a null character (i.e., a character with an ASCII value of 0). For the data to display correctly, you need to extract just the portion of the Computer_Name data that comes before the terminating null character. The following expression does this:

Left(strComputerName, InStr(strComputerName, vbNullChar) - 1)

The ADO code in this solution will work in Access 2000, but the AddItem method won't. You can use string concatenation to build up the value list in Access 2000, but be aware that value lists in Access 2000 are limited to 2,048 characters; this limit was increased to over 32,000 characters in Access 2002.

The ListConnections procedure is called from both the Load event of the form and the Click event of the Refresh button:

Private Sub Form_Load( )
    ListConnections
End Sub

Private Sub cmdRefresh_Click( )
    ListConnections
End Sub

In addition to the technique used in this solution, you can monitor the users in your application by using a utility that is available as a free download from Microsoft at http://support.microsoft.com?kbid=1863. This LDB viewer will work with Access 97, which used Version 3.51 of the Jet engine. The code in this solution is supported only by Jet Version 4.0 or later.