By using Microsoft Visual Basic for Applications in
Microsoft Access 2000, you can output a list of users who are logged into a
database. The code example in this article show you how to do this.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The user list feature provides a way of determining
who is currently connected to a Microsoft Jet database. The list can be
obtained via the ADO programming interface and returns the following
information for each user:
- Name of the computer that the user is
- Security name, that is, the user ID.
- Whether or not the user is currently connected to the
database. (A user's ID remains in the lock database until the last user
disconnects or until the slot is reclaimed for a new user
- Whether or not the user connection was terminated
- The user list capability can be used in conjunction with
the connection control capability to determine which users are still connected,
so that they can be asked to disconnect.
The user list capability is also useful in isolating problems
with database corruption that is associated with the activities of a specific
To demonstrate the code example, follow these
: The sample code in this article uses Microsoft ActiveX Data
Objects. For this code to run properly, you must reference the Microsoft
ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References
on the Tools
menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library
check box is selected.
- Copy the Northwind sample database the C:\ folder.
- In Microsoft Access, open C:\Northwind.
- In the Database Window, click Modules under Objects, and then click New.
- Save the module as ShowUsers.
- Type the following code in the new ShowUsers module:
Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=c:\Northwind.mdb"
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\Northwind.mdb"
' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets
Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
'Output the list of all users in the current database.
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
- Close the Northwind database and when prompted, save
changes to ShutDownDB (This must be done to release an exclusive lock on the
database due to the code you have just added).
- Re-open C:\Northwind.
- Press CTRL+G to open the Immediate Window in the Visual
- Type the following in the Immediate window and press
Note that in the Immediate window returns a list of users in the
Article ID: 198755 - Last Review: June 23, 2005 - Revision: 4.0
- Microsoft Access 2000 Standard Edition
|kbhowtomaster kbinfo kbprogramming kbado kbvba KB198755|