Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    List of Users Logged on (2000 & XP)

    I found the following code on the Microsoft web site that displays in the immediate window a list of users logged on to a shared database. Unfortunately it does not work when the database is secured with an .mdw file. The error message that one does not have the necessary permissions to access this database comes up even though I am already logged on as a legitimate user with admin privileges. Does anyone know how to modify this code such that it will work in a secured environment?

    Sub ShowUserRosterMultipleUsers()
    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=e:my documentspeterbusinesswestowerjobbookwestower jobs v4.mdb"

    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=v:03 - westower sharedjob bookwestower jobs v4.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

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List of Users Logged on (2000 & XP)

    I modified the MSKB sample code as follows:

    <code>Public Sub EnumerateLoggedInUsers()</code>
    <code> On Error GoTo Err_Handler</code>
    <code></code>
    <code> Dim rst As ADODB.Recordset</code>
    <code> Dim cnn As ADODB.Connection</code>
    <code> Dim strCnn As String</code>
    <code> Dim n As Long</code>
    <code> Dim strMsg As String</code>
    <code> </code>
    <code> ' MSKB 198755, HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000</code>
    <code></code>
    <code> Set cnn = New ADODB.Connection</code>
    <code> strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _</code>
    <code> "User ID=MarkD;" & _</code>
    <code> "Password=MD123;" & _</code>
    <code> "Data Source=C:AccessSecuredNW.mdb;" & _</code>
    <code> "Jet OLEDB:System database=C:AccessSecuredSecured.mdw;"</code>
    <code> </code>
    <code> cnn.Open strCnn</code>
    <code> Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")</code>
    <code> </code>
    <code> Do Until rst.EOF</code>
    <code> For n = 0 To 3</code>
    <code> Debug.Print rst.Fields(n).Name, rst.Fields(n)</code>
    <code> Next n</code>
    <code> rst.MoveNext</code>
    <code> Debug.Print ' blank line</code>
    <code> Loop</code>
    <code> </code>
    <code>Exit_Sub:</code>
    <code> If rst.State = adStateOpen Then rst.Close</code>
    <code> If cnn.State = adStateOpen Then cnn.Close</code>
    <code> Set rst = Nothing</code>
    <code> Set cnn = Nothing</code>
    <code> Exit Sub</code>
    <code></code>
    <code>Err_Handler:</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> Beep</code>
    <code> MsgBox strMsg, vbExclamation, "ERROR MESSAGE"</code>
    <code> Resume Exit_Sub</code>
    <code>End Sub</code>

    For test purposes, created secured copy of Northwind.mdb (NW.mdb) and created two user accounts in addition to myself (Admin/Owner). I opened two separate instances of Access, logged into secured db as each of these two users. Then opened 3rd instance of Access, in a non-secured db, and ran the above procedure. Results as output to Immediate window:

    COMPUTER_NAME DELL4600C
    LOGIN_NAME LarryE
    CONNECTED True
    SUSPECT_STATE Null

    COMPUTER_NAME DELL4600C
    LOGIN_NAME BillGatesIII
    CONNECTED True
    SUSPECT_STATE Null

    COMPUTER_NAME DELL4600C
    LOGIN_NAME MarkD
    CONNECTED True
    SUSPECT_STATE Null

    The output above correctly reflects each logged-in user, including myself (an Admin account must be used to open secured connection). The main requirement is, when specifying connection string, you must provide path of the workgroup file, in addition to UserID and Password for a user with Admin permissions. Note: Tested with A2K, WIN XP system, reference set to ADO 2.1 library.

    HTH

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: List of Users Logged on (2000 & XP)

    PS: Should be mentioned, if you are currently logged in to secured database as a user with Admin permissions, then you can simply use the current connection to open schema recordset. Modified example:

    <code>Public Sub EnumerateLoggedInUsers()</code>
    <code> On Error GoTo Err_Handler</code>
    <code></code>
    <code> Dim rst As ADODB.Recordset</code>
    <code> Dim cnn As ADODB.Connection</code>
    <code> Dim n As Long</code>
    <code> Dim strMsg As String</code>
    <code> </code>
    <code> Set cnn = CurrentProject.Connection</code>
    <code> Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")</code>
    <code> </code>
    <code> Do Until rst.EOF</code>
    <code> For n = 0 To 3</code>
    <code> Debug.Print rst.Fields(n).Name, rst.Fields(n)</code>
    <code> Next n</code>
    <code> rst.MoveNext</code>
    <code> Debug.Print ' blank line</code>
    <code> Loop</code>
    <code> </code>
    <code>Exit_Sub:</code>
    <code> If rst.State = adStateOpen Then rst.Close</code>
    <code> Set rst = Nothing</code>
    <code> Set cnn = Nothing</code>
    <code> Exit Sub</code>
    <code>Err_Handler:</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "ERROR MESSAGE"</code>
    <code> Resume Exit_Sub</code>
    <code></code>
    <code>End Sub</code>

    When tested this procedure, logged in as Admin, with the other users logged in to same secured database, correct results were returned, as shown previously. If you (as Administrator) are the only one who will be running this procedure, then you can use the simpler example above.

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List of Users Logged on (2000 & XP)

    In 2002, you can use the OpenSchema to get this information without even having a reference set to the ADO library. I built a utility app for my company that does this using the built in ADO support in XP and late binding.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: List of Users Logged on (2000 & XP)

    Many thanks Mark and Charlotte. That works fine.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List of Users Logged on (2000 & XP)

    The code posted above works great...

    however, does anyone know how you can get the windows logged in name, from the machine name.. if its possible..

    ie: Machine GH59393.... but the user maybe be e78694

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List of Users Logged on (2000 & XP)

    I don't know if it's possible to do that - and if so, you might need to have administrator permissions to get at the info.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •