access db logon list STILL looking for code help

[phinds]

I just found this code (most of it) right here a couple of days ago. It is for the purpose of finding out who is logged on to an access db and co-incidentally a guy down the hall asked me to help him do just that so I thought I'd try to get this to work for me. I've lost track of the thread I sucked it off of and can't find it in search.

my db connection opens just fine but I get error message 3251 "operator or provider is not capable of performing requested operation" at the line commented below.

I commented out the line "If adxUsr.Name = cmbLogonId " 'cause it was giving a compiler error and I figured I"d get back to it when I got the rest working, but execution is halting at the commented line.

I'm flying somewhat blind on this since I have not used these constructs before (catalog / group / user). On-line help isn't helping, but that's a common problem for me.

My db references are

MS ActiveX Data Objects 2.7 Library
MS ADO Ext. 2.7 for DLL and Security

Option Explicit
Public cn As ADODB.Connection
'
' close db on cmd button pushed
'
Private Sub cmdClose_Click()
    cn.Close
    Set cn = Nothing
End Sub
'
' open db on cmd button pushed
'
Private Sub cmdOpen_Click()
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & "M:\SVC DEL COORD\open_items97.mdb"
    cn.Open
End Sub
'
' find user on cmd button pushed
'
Private Sub Command1_Click()
    Dim adxCat As ADOX.Catalog
    Dim adxGrp As ADOX.Group
    Dim adxUsr As ADOX.User
    Dim cnt As Integer
 
    Set adxCat = New ADOX.Catalog
    Set adxGrp = New ADOX.Group
 
    cnt = 1
 
    adxCat.ActiveConnection = cn
    For Each adxGrp In adxCat.Groups ' <== ERROR MSG HERE <==
        lstGroups.AddItem adxGrp.Name
        cnt = cnt + 1
    Next
 
    Set adxCat = New ADOX.Catalog
    Set adxGrp = New ADOX.Group
    Set adxUsr = New ADOX.User
 
    For cnt = 0 To lstGroups.ListCount - 1
        lstGroups.Selected(cnt) = False
    Next cnt
 
    Set adxCat = New ADOX.Catalog
    Set adxUsr = New ADOX.User
    Set adxGrp = New ADOX.Group
 
    adxCat.ActiveConnection = cn
 
    For Each adxUsr In adxCat.Users
'        If adxUsr.Name = cmbLogonId Then
            For Each adxGrp In adxUsr.Groups
                For cnt = 0 To lstGroups.ListCount - 1
                    If adxGrp.Name = lstGroups.List(cnt) Then
                        lstGroups.Selected(cnt) = True
                    End If
                Next cnt
            Next
'        End If
    Next
    Set adxGrp = Nothing
    Set adxUsr = Nothing
    Set adxCat = Nothing
End Sub

any help will be appreciated

[Mandelbrot]

PHinds,


Try using the LDB file in the same folder as the database file. This stores the name of the Access database user. If you have a central datastore then you'll need to use the LDB stored in the same folder as this file.

With XP, 2000 and NT you can obtain the current user name by asking for the einvironment variable:

Debug.Print Environ$("USERNAME")

But this will only return the user of the computer, not the database user.

Hope this helps.


Regards...

[phinds]

Mandelbrot, thank you. The LDB file does contain useful info.

I'd still like to get the code sample working though, so if anyone can help with that, I'd appreciate it.

[peet]

phinds, have not tried the code you posted, sorry.

I have however a way of showing users currently logged into an access db.

Private Sub Command1_Click()
    ShowUserRosterMultipleUsers
End Sub
 
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=C:\TEST\From.mdb"
 
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=C:\TEST\From.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

this works. Taken from the MSDN

[peet]

found the article : http://support.microsoft.com/directo...EN-US;Q198755&

tells you more about the sample and how it works.

[phinds]

Peet, thank you. This works although it tells me I'm logged on whether I am or not but this seems to be because I'm the db admin and it lists the admin whether anyone is logged on or not.

the "{947bb102-5d43-11d1-bdbf-00c04fb92675}" is certainly intuitively obvious; why didn't I think of that? I just love the straightforward way MS does these things. I'll check out the whole thing --- thanks for providing the reference and thanks again for the solution.

SO ... you've solved my immediate problem, but I'd STILL like to get my code snippet working because
(1) it apparently has the ability to report on groups as well as users, which would be nice to know
(2) I'm a persistant bastard
(3) I HATE mysteries such as why the &^$#& doesn't it work?

[peet]

Originally posted by phinds
the "{947bb102-5d43-11d1-bdbf-00c04fb92675}" is certainly intuitively obvious; why didn't I think of that? I just love the straightforward way MS does these things. I'll check out the whole thing --- thanks for providing the reference and thanks again for the solution.

I do agree

I'll give the code above a go later on, and let you know if I'm able to find out anything useful

[phinds]

bump

[phinds]

bump one more time