"Seeing" current users in a database

T

Therat

Access Denied
Local time
Today, 18:22
Joined
May 21, 2002
Messages
53
Is it possible to create a function in access that can "see" how many current users are in a particular database? In a perfect world I could narrow it down to a PC address/user, but I can live with function that can tell if there is someone else in the db. If so, can you give me a hand? And I don't mean clap!

:)
 
Like this?

This code is behind the OnClick event for a button on a form, but you wqill need to open the immediate window to see the results. :cool:

Sub ShowUserRosterMultipleUsers()
On Error GoTo err_ShowUserRosterMultipleUsers

Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Dim strPath As String

strPath = InputBox("Please enter the exact path to the database you wish to check.", "Who's Here?")

If strPath <> "" Then
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & strPath

cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strPath

' 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 If

exit_ShowUserRosterMultipleUsers:
Exit Sub

err_ShowUserRosterMultipleUsers:
Resume exit_ShowUserRosterMultipleUsers

End Sub
 
Still need a little help...

I created a generic form with a button. In the button for the "on-click" event, I have an event procedure with your code. As I press the button, the input box appears asking for the location. So far so good. However, when I type in the location of the db, the input box disappears and nothing happens. Is it something I missed? Your instructions say open the immediate window. What does that mean? thanks.
 
sorry for the delay...

Yes, you need to open the Immediate Window to see the results.

Open any code behind form or module. Then click on the View menu, then Immediate Window. You will then find your results.

;)
 
Sam_F,

Is it possible to use that sub to check the users in a secured db? I woul like to check the backend of a secured db that has Access securtiy with a Workgroup name and User password.

Thanks!
 
ghudson:

Don't see why not but really don't know. Beleive I found thsi on the FMS site... here is more info...

Tip #2: Using the User Roster to Check for Users in a Database
Provided by : Troy Munford, FMS Development Operations Manager

Version 4.0 of the OLE DB Provider for Microsoft Jet added support for several new schema rowsets that were not previously available. The most notable of these is the User Roster schema rowset. The user roster is exposed as a provider-specific schema rowset in the Jet 4 OLE DB provider. Since provider-specific schemas are not listed in ADO's type library for schema rowsets, you must use a GUID to reference the schema like this one.

The 4 things that can be returned for this schema are:


Name of the computer that the user is using.

Security name.

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 connection.)

Whether or not the user connection was terminated normally.

from:
http://www.fmsinc.com/free/tips.html#Usingtheuserroster
 
Further Question......

I have attempt to integrate the above "whoson" form into a db, but it does not work, and upon attempting to run it, it debugs to

Private Function WhosOn() As String

On Error GoTo Err_WhosOn

Dim iLDBFile As Integer, iStart As Integer
Dim iLOF As Integer, i As Integer
Dim sPath As String, x As String
Dim sLogStr As String, sLogins As String
Dim sMach As String, sUser As String
Dim rUser As UserRec ' Defined in General
Dim dbCurrent As DAO.Database

' Get Path of current database. Should substitute this code
' for an attached table path in a multi-user environment.

Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
sPath = dbCurrent.Name
dbCurrent.Close

The notes state to subsitute "this code for an attached table path," but how should this be named? ie In the above last 3 lines of code, should there be a file location path entered into this part? If so, where?

Many thanks for any suggestions
 
You need to set a referrence to DAO "Microsoft DAO 3.6 Object Library"

This is how to link to an external db to see who is in it using the WhoIsOn()...
Code: 
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
'sPath = dbCurrent.Name
sPath = "\\Server\Partition\Directory\Database.mdb" 'File location of the backend "data"
dbCurrent.Close

I have been using the form and code that Oldsoftboss linked to for years. It works but I was intriged by the code that sfreeman provided. I had saved the link to the code that sfreeman provided HOW TO: Determine Who Is Logged onto a Database by Using Microsoft Jet the first time I saw it last year but I could never get it to work the way I wanted. Now that somebody else has brought it up I would like to get it to work so that I can use the code to see who is in a secured database [from another database]. Anybody got a clue how to do it?
 
I know this is an old post but it was the best one I could find with what i was trying to do.

Code: 
Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long

    Set cn = CurrentProject.Connection

    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 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

But I could only get it to work in the current DB not the Backend DB. So I tried sfreeman version:

Code: 
Sub ShowUserRosterMultipleUsers()
On Error GoTo err_ShowUserRosterMultipleUsers

Dim cn As New ADODB.Connection
Dim cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Dim strPath As String

strPath = InputBox("Please enter the exact path to the database you wish to check.", "Who's Here?")

If strPath <> "" Then
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open "Data Source=" & strPath

cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strPath

' 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 If

exit_ShowUserRosterMultipleUsers:
Exit Sub

err_ShowUserRosterMultipleUsers:
Resume exit_ShowUserRosterMultipleUsers

End Sub

strPath = InputBox("Please enter the exact path to the database you wish to check.", "Who's Here?")

Instead I used:

strPath = "\\MCRATS\Database\MCRATS_be.mdb"

Any help as to what i am doing wrong. I can't get it to work to check Backend of the DB?
 
Last edited:
I do this quite crudely. Onstartup the database runs a batchfile that outpust the users IP address to a linked table. I know what everyones IP address is so i can link names and time to the user.
 
You must log in or register to reply here.

Similar threads

J
How to see the UN and PC Name of locked Records
Replies
14
Views
389
jmark@stayintouch.us
J
B
Advice Request : Simplest method to allow multi-site access to a prototype database
Replies
10
Views
399
Pat Hartman
P
W
Windows 11 Slowing Down Database
2
Replies
22
Views
641
MagicIT
MagicIT
B
Expanding related tables and splitting database
Replies
10
Views
388
Pat Hartman
P
K
A dream: What I do miss about Access.
2
Replies
20
Views
796
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom