Access Cookbook - Ken Getz [219]
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 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.
10.9. Determine if a Record Is Locked and by Whom
Problem
When you use pessimistic locking (discussed in the upcoming sidebar) in your applications, Access informs you if another user has locked a record by displaying an icon in the record selector of the form's detail section (shown in the upper-left corner of Figure 10-24). While this is nice, you may want to know who actually has the record locked. Is there any way to determine this?