The Jet User Roster Form

As I was putting this chapter together, I kept hunting for a surveillance tool that would be easy for readers to implement. Finally, I decided on developing an Access 2000 form called frmJetUserRoster (shown in Figure 6-1) that is included in the demonstration database. You can import this form into your database and it should work straight away. The descriptions about the technology behind the form are complex, and you can skip those discussions if you're happy with the way the form works.

Figure 6-1: The Jet User Roster form, which shows users in a database.

So what does frmJetUserRoster do? When the form opens, the onLoad event triggers VBA code to generate a list of computer names, Access workgroup names , and two codes that show the state of the users' connection to the current database.

Note	Workgroup security is also referred to as user-level security in Access help manuals and other Access books.

This list includes all the users who are currently connected to the database. The user list is obtained by using an ActiveX Data Objects (ADO) schema rowset (see the description following) that has been specially written for Access databases. Adding the frmJetUserRoster form to your database is relatively hassle free because the ADO library is fully set up on any computer that has a default installation of Access 2000 and 2002. This form will work in Access 97 and will work with a reference to the Microsoft ActiveX Data Objects 2.1 Library (or later).

Note

ADO consists of three libraries: 1) the Microsoft ActiveX Data Objects 2.x library that I use in this chapter, 2) the Microsoft ADO Ext. 2.1 for DDL and Security, and 3) the Microsoft Jet and Replication Objects 2.1 library. You don't need a library reference to either of the latter two for anything demonstrated in this chapter. Find out more in the "Further Reading" section at the end of this chapter.

The list box on the frmJetUserRoster demonstration form displays the following information:

Computer Name , which identifies the workstation as specified in the Control Panel under Network for NT/Windows 98 or System for Windows XP/2000.
Login Name, which specifies the Access workgroup user name the user entered to log on to the database. If the user name and password prompt does not appear, this field returns Admin, the default Access user name. An exception to this rule is when a Windows shortcut file or an executable supplies the user name and password to open the database.
Connected, which returns True (-1) if there's a corresponding user lock in the .LDB file. If the connection is False, this column indicates issues that may lead to corruption of the database.
Suspect State, which returns True (-1) if the user has left the database in a suspect state. This state can happen if a computer loses power, Windows crashes, or other unnatural events occur. Otherwise, this value is Null.

Note	Connection and suspect state problems indicate that you may need to review issues that cause corruption in the database. See Microsoft Knowledge Base Article No. 109953 for more information.

Preventing More Users From Logging On

One of the features of the frmJetUserRoster form is a check box that allows you to stop additional users from logging on to the database. Once you have selected the Stop Additional Users check box, you can be confident that no new users will connect or open the database. If a user opens the database, he or she will receive a warning (shown in Figure 6-2) and will be shut out of the database.

Figure 6-2: Warning received when users try to connect to a database that has been locked.

Note

Database Administrators: Introduced in Access 2000, formal control of the development process is available to you through shared and exclusive ownership of the database. This control is important because you will regularly be involved with issues that arise from exclusive ownership requirements when you are trying to upgrade the database. In all versions of Access, you will need exclusive rights to the database to undertake very necessary maintenance, such as compacting and repairing. That's why obtaining user lists and stopping more people from logging on to the database is very important.

Retrieving the User Roster by using VBA Code

When you click the Update List button (shown in Figure 6-1), you start the following VBA subroutine. This subroutine includes establishing your ADO connection by using the CurrentProject object. Now a recordset is established because the OpenSchema method of the Connection object responds to a unique text string, called a global unique identifier (GUID); specifically , {947bb102-5d43-11d1-bdbf-00c04fb92675}.

 Private Sub updateList_Click() ' Find a list of users in an Access database by using the JetUserRoster ' data provider that is provided with the ADO library. Dim adoRS As New ADODB.Recordset Dim i As Long, j As Long, strUserList As String On Error GoTo updateList_Error strUserList = "" Set adoCn = CurrentProject.Connection Set adoRS = adoCn.OpenSchema(adSchemaProviderSpecific, , _ "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

Now that you have established the recordset, you will find that it has a row for every Access session that has opened the database plus four columns of information, as illustrated in Figure 6-1.

A four-column list box then shows the recordset by using the following steps:

The field names of the recordset produce the headers.
The four columns of the schema information are added to these headers, and all this information is stored in a semicolon-delimited text string.
To display the string, set the rowsource property of the list box to the text string with the schema headers and semicolon-delimited data.

Note	You may need look at the properties of the list box in the form to see exactly how this is displayed.

 ' Generate the list of all users in the current database. strUserList = adoRS.Fields(0).Name & ";" & adoRS.Fields(1).Name & _             ";" & adoRS.Fields(2).Name & ";" & adoRS.Fields(3).Name & ";" While Not adoRS.EOF   strUserList = strUserList & TrimToNull(adoRS.Fields(0)) & ";" & _                  TrimToNull(adoRS.Fields(1)) & ";" & _                  TrimToNull(adoRS.Fields(2)) & ";" & _                  TrimToNull(adoRS.Fields(3)) & ";"   adoRS.MoveNext Wend DisplayJetRoster.RowSource = strUserList

Now the form procedure uses a special property of the ADO connection object, which allows us to stop any more users from logging on to the database. This property can be set to True or False, as shown here, and will work only if you select the Stop Additional Users check box.

 Const NewConnectStop = 1 Const NewConnectOK = 2 If chkStopMoreUsers = True Then ' Stop additional users from logging on to the database.   adoCn.Properties("Jet OLEDB:Connection Control") = NewConnectStop   MsgBox "The database is now locked to new users" & _    vbCrLf & vbCrLf & "Open a new instance of the database to see the warning " & _    "that the user receives when the database is locked", _    vbInformation, "Database Locked." ElseIf chkStopMoreUsers = False Then ' You can now allow users to open the database again.   adoCn.Properties("Jet OLEDB:Connection Control") = NewConnectOK End If

The user roster recordset isn't quite as simple to decode as the Microsoft MSDN examples lead you to believe. If you try to display the user list in a combo box, a list box, or save the information in the table, you will find that the information in the recordset has a null character at the end of each field of text. This null can create some unwanted outcomes . To resolve this problem, I've written a function called TrimToNull that removes these unnecessary characters from the end of the string (as follows ).

 Private Function TrimToNull(valueReq As Variant) As String ' Make the value a text string. ' Remove all values after the null character. Dim trimStr As String Dim nullPos As Long   On Error GoTo TrimToNull_Err   trimStr = CStr(valueReq) ' Locate the terminating null (if any).   nullPos = InStr(1, trimStr, Chr$(0))   If nullPos > 0 Then ' Return the characters before it.     trimStr = Left(trimStr, nullPos - 1)   Else     trimStr = trimStr   End If TrimToNull_Exit:   TrimToNull = trimStr   Exit Function TrimToNull_Err:   trimStr = ""   Resume TrimToNull_Exit End Function

If you find that you want to extend this software to use your own ADO Connection string rather than the CurrentProject Connection string, you may need to read more about connecting to password-and workgroup-secured databases. I provide a link to an article on this material at www.vb123.com in the "Further Reading" section of this chapter, or you might just want to use the Access Workbench, which handles this problem by securely storing your security information.