Microsoft Office 2000/Visual Basic Programmer's Guide

Identifying Users

In your multiuser solution, you may want to programmatically identify the user who is currently logged on to the system. This is useful for administrative functions such as storing the user's name with edited records to create an audit trail. Several methods are available to achieve this functionality. One involves Microsoft Jet user-level security, another uses the ADO OpenSchema method to retrieve information about the users in the database.

Checking a User's Logon ID

When you establish user-level security for your database, you force the user to log on to your solution with a predefined user name and password. From ADO code, the user name is then available to your solution through the User ID property of the Connection object. The following procedure writes the user's name and the current date and time to fields in a table named AuditTrail in the current database.

Sub WriteAuditTrail()
   Dim rst As ADODB.Recordset
   Dim cnn As ADODB.Connection
   
   Set cnn = CurrentProject.Connection
   
   ' Open AuditTrail table.
   Set rst = New ADODB.Recordset
   rst.Open "AuditTrail", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
   With rst
      ' Add new record.
      .AddNew
      ' Write name of current user to table.
      !UserLastModified = cnn.Properties("User ID")
      ' Write time to table.
      !DateLastModified = Now
      .Update
   End With
End Sub

The WriteAuditTrail procedure can be found in the MultiuserIssues module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.

For more information about user-level security, see Chapter 18, "Securing Access Databases."

Retrieving User Information with the OpenSchema Method

ADO and the Microsoft Jet 4.0 OLE DB Provider also supply a way to retrieve information about which users are in the database by using a schema query. ADO provides schema queries to furnish a variety of information about a database. You use the ADO OpenSchema method to run a schema query and return values to a Recordset object, which you can then use to read the information. For details about using the OpenSchema method, search the ADO Help index for "OpenSchema method."

To run a schema query that returns information about the users who currently have a shared Access database open, you must specify the adProviderSpecific constant for the Schema argument of the OpenSchema method, and pass a globally unique identifier (GUID) value that identifies the query to the Jet provider as the SchemaID argument. The following code demonstrates how to do this.

Sub OpenJetUserInfo(strDbPath As String)
   Dim cnn    As ADODB.Connection
   Dim rst    As ADODB.Recordset
   Dim fld    As ADODB.Field
   Dim strConnect As String
   
   ' Format connection string to open database.
   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath
   
   Set cnn = New ADODB.Connection
   cnn.Open strConnect
   
   ' Open user information schema query.
   Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, _
      SchemaID:="{947bb102-5d43-11d1-bdbf-00c04fb92675}")

   ' Print user information to the Immediate pane.
   With rst
      Do Until .EOF
         For Each fld In .Fields
            Debug.Print fld.Name, fld.Value
         Next fld
         .MoveNext
         Debug.Print
      Loop
   End With
End Sub

The OpenJetUserInfo procedure can be found in the MultiuserIssues module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.

The following table describes the information contained in each column returned by the schema query.