7.2 Create a Global Procedure Stack

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