Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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