ACC2000: Checking Who Logged into Database with Jet UserRoster

ID: Q198755


The information in this article applies to:

Advanced: Requires expert coding, interoperability, and multiuser skills.


SUMMARY

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp


MORE INFORMATION

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:

The user list capability is also useful in isolating problems with database corruption that is associated with the activities of a specific user.

To demonstrate the code example, follow these steps:
  1. Copy the Northwind sample database the C:\ folder.


  2. In Microsoft Access, open C:\Northwind.


  3. In the Database Window, click Modules under Objects, and then click New.


  4. Save the module as ShowUsers.


  5. Type the following code in the new ShowUsers module:


  6. 
    Sub ShowUserRosterMultipleUsers()
        Dim cn As New Connection
        Dim cn2 As New Connection
        Dim rs As New 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, _
        , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    
        '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), _
            rs.Fields(2), rs.Fields(3)
            rs.MoveNext
        Wend
    
    End Sub 
  7. 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).


  8. Re-open C:\Northwind.


  9. Press CTRL+G to open the Immediate Window in the Visual Basic Editor.


  10. Type the following in the Immediate window and press ENTER:


  11. 
    ShowUserRosterMultipleUsers 
    Note that in the Immediate window returns a list of users in the database.

Additional query words: inf


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999