Show user/s that is using the database

[megatronixs]

Hi all,

I found some code that I tried to use to show the user who has the database currently open, but I have no clue how to change it so the name of the environ as it will be more sense then the computer name and role of the user,

Option Compare Database
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:\Database\Test\Test_Working_on.mdb"
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source="C:\Database\Test\Test_Working_on.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

Greetings.

 

[Mile-O]

Store the user's environ code in the user table, and then look it up when needed?

 

[megatronixs]

Hi Mile-o,

I have a table with the user eviron. it is called tbl_Users. How could I use that part?
It is just confusing for me as I still have plenty to learn but it should be implemented yesterday
Can I use the eviron somewhere below?

    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

Greetings.

 

[Mile-O]

Which of the fields returned is your users' environ code: 0, 1, 2, or 3?

Just a basic DLookup() would get you the information from tbl_Users with the environ code as criteria.

eg

UserName = Nz(DLookup("Forename", "tbl_Users", "EnvironCode = """ & rs.Fields(??) & """")) & Nz(DLookup("Surname", "tbl_Users", "EnvironCode = """ & rs.Fields(??) & """"))



or, write the findings into a table, and use a query to connect on the environ code...

 

[megatronixs]

Hi,
THe eviron is on the first field (0) in the tbl_Users.
But I'm still lost how to do this :-(

Greetings.

 

[megatronixs]

Closing this as it is to hard to solve.

Greetings.