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?

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.

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

  Set cnn = CurrentProject.Connection
  Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _
    "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
  lboConnections.RowSource = "" 
  lboConnections.AddItem "Computer Name;Login Name"

  Do While Not rst.EOF
    If rst!Connected = True Then
      lboConnections.AddItem Left(rst!Computer_Name, _
       InStr(rst!Computer_Name, Chr(0)) - 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 = ""

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(rst!Computer_Name, InStr(rst!Computer_Name, Chr(0)) - 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/support/kb/articles/Q186/3/04.ASP.

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.