Use ADO to Return a List of Users Connected to a Database

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Use ADO to Return a List of Users Connected to a Database

by Susan Sales Harkins

Application: Access 2000
Operating System: Windows

Any database administrator will tell you that a networked Access database can be a problem to maintain. To make changes, the administrator must open the database exclusively, which means no one else can have the database open. Either everyone must stop his work and close the database while the administrator tends to maintenance tasks, or the administrator must schedule chores during company off-hours.

As if that weren't enough to contend with, consider this one last annoying situation: You're the administrator and you've scheduled downtime or you're working on a Saturday while everyone else is enjoying the day off. You try to open the database exclusively only to learn that someone already has it open. Obviously, someone went home and left his copy open and running.

With earlier versions of Access, there isn't an easy way to find the system that's still running the database. However, Access 2000 offers the administrator a simple Access solution for this situation--using ADO's schema recordsets. You're probably familiar with recordsets; they contain data from your tables. Schema recordsets, however, contain information about the database itself.

How to open a schema recordset

To open a schema recordset you'll use the Connection object's OpenSchema method in the form

connection.OpenSchema(querytype, _
    criteria, schemaID)

where connection identifies the Connection object and querytype is an intrinsic constant that tells ADO what kind of information you want. Criteria is an optional argument that filters the resulting recordset. The last parameter, schemaID, is a GUID that identifies a specific schema. This parameter is necessary only when querytype equals adSchemaProviderSpecific. For the Microsoft Jet OLE DB Provider, this constant returns four different schema recordsets:

  • A recordset of current users of the database (this is the one we'll be working with in this article)
  • A recordset of partial replica filters
  • A recordset of replica conflict tables
  • A recordset of ISAM statistics

For your convenience, Table A lists the global constants and GUIDs that apply to the Jet provider.

Table A: Jet OLE DB provider-specific constants

Constant GUID
JET_SCHEMA_USERROSTER {947bb102-5d43-
11d1-bdbf-
00c04fb92675}
JET_SCHEMA_REPLPARTIALFILERLIST {e2082df0-54ac-
11d1-bdbb-
00c04fb92675}
JET_SCHEMA_REPLCONFLICTTAGBLES* {e2082df2-54ac-
11d1-bdbb-
00c04fb92675}
JET_SCHEMA_ISAMSTATS {8703b612-5d43-
11d1-bdbf-
00c04fb92675}

*****This is written as it appears in Microsoft's documentation. Feel free to correct when you declare constants in a procedure.

Returning the current users

Now we're ready to tackle the actual problem--an ADO procedure that will identify the current users of a database. For that purpose, we'll create a procedure that returns the current users of the Northwind sample database that comes with Access. If you try our procedure, be sure you use your system's correct path to Northwind.mdb--yours may not be the same as ours. First, launch Northwind, open a blank module, and enter the global constant

Global Const JET_SCHEMA_USERROSTER = _
    "{947bb102-5d43-11d1-bdbf-00c04fb92675}"

in the module's General Declarations section. You could, if you like, replace the JET_SCHEMA_USERROSTER argument in our code with the actual value "{947bb102-5d43-11d1-bdbf-00c04fb92675}" and omit this statement. However, using the constant will make your code more readable. Later, you may have trouble remembering just what that long string of values means. Next, enter the procedure shown in Listing A.

Listing A: ReturnUserRoster() function

Sub ReturnUserRoster()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Program Files\Microsoft " & _
    "Office\Office\Samples\Northwind.mdb;"
Set rst = cnn.OpenSchema(adSchemaProviderSpecific _ 
    ,   , JET_SCHEMA_USERROSTER)
Debug.Print rst.GetString
Set rst = Nothing
Set cnn = Nothing
End Sub

Let's examine what this procedure does. After declaring the Connection and the Recordset objects, the Open method creates a connection to the Northwind.mdb database and then sets the rst object using the adSchemaProviderSpecific and JET_SCHEMA_USERROSTER arguments we discussed in the previous section. The resulting recordset will consist of one record for each current user in the database. The GetString method returns the recordset as a string and the procedure uses this method to print the recordset in the Immediate window. You could also send the results to a file, display it in a message box, or store the results in a table (which would take a bit more work than we've shown).

This particular schema recordset contains the following information:

  • COMPUTER_NAME. Identifies the workstation as specified in the system's Network control panel.
  • LOGIN_NAME. Specifies the name the user entered to log into the database, if it's secured. If it isn't secured, this field returns Admin.
  • CONNECTED. Returns True (-1) if there's a corresponding user lock in the LDB file.
  • SUSPECTED_STATE. Returns True (-1) if the user has left the database in a suspect state. Otherwise, this value is Null.

If you'd like to see the results, press [Ctrl]G to display the Immediate window. Then, type ReturnUserRoster and press [Enter] to run the procedure. Figure A shows the results of the procedure on our system.

Figure A: Our ReturnUserRoster lists all of the users currently accessing the Northwind database.

Note that there appears to be a duplicate entry, KNIGHTRIDER. This is the name of the computer that we're running the procedure from. Since we ran the procedure from within the Northwind database, we actually have two connections to it--one from opening the database directly through Access and one created by the following statement in our procedure:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Program Files\Microsoft " & _
    "Office\Office\Samples\Northwind.mdb;"

Using the information shown in Figure A, we learn that there are three computers with a connection to the database, the database isn't secured, there are corresponding user locks, and that the database isn't in a suspect state. A suspect state can indicate that the database may need to be repaired, such as after it's improperly closed.

Make your job easier

Chasing down a stray open database can be a real problem for administrators. Fortunately, ADO makes this job much easier with the addition of schema recordsets, which return information about the database. In this article, we showed you how to use this new feature to return a list of current users for a networked database.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.