Then live without it.
Public Function ADOShowUserRosterToString(cnnConnection As ADODB.Connection) As String
' Comments: Uses the new Jet 4 User Roster to list all users in the specified database
' Params : cnnConnection Open ADODB connection to the Jet Database
' Returns : String of all users seperated by a new line
' Source : Total Visual SourceBook
Dim rstTmp As New ADODB.recordSet
Dim strTmp As String
' This is the value to pass to Jet to get the user roster back.
Const cstrJetUserRosterGUID As String = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
On Error GoTo PROC_ERR
' Jet exposes the user roster as a provider-specific schema rowset.
' To get Jet to return this, we open a recordset and pass the special GUID value.
Set rstTmp = cnnConnection.OpenSchema(adSchemaProviderSpecific, , cstrJetUserRosterGUID)
' The recordset contains four fields:
' COMPUTER_NAME: The machine name of the user's computer
' LOGIN_NAME: The name the user logged into Access with
' CONNECTED: True if the user is still connected
' SUSPECT_STATE: Connection was terminated normally or not (generally returns nothing if the user terminated normally or is still in the database)
With rstTmp
Do Until .EOF
strTmp = strTmp & _
.Fields(0).Name & ":" & Trim(.Fields(0).value) & ", " & _
.Fields(1).Name & ":" & Trim(.Fields(1).value) & ", " & _
.Fields(2).Name & ":" & Trim(.Fields(2).value) & ", " & _
.Fields(3).Name & ":" & Trim(.Fields(3).value) & vbCrLf
.MoveNext
Loop
End With
rstTmp.Close
ADOShowUserRosterToString = strTmp
PROC_EXIT:
Exit Function
PROC_ERR:
MsgBox "Error: " & err.Number & ". " & err.Description, , "ADOShowUserRosterToString"
Resume PROC_EXIT
End Function
'************ End Function Code ************
'************ Begin Example Code ************
' To call the above function, all you need to do is open connection to the database.
' This example code shows the connection you create in code here, as well as any other users in the database.
Private Sub TEST()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
' Open the connection
With cn
.CursorLocation = adUseServer
'.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Tmp.accdb"
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\test.accdb;Mode=Share Deny None;Extended Properties="""""
End With
' Example code for ADOShowUserRosterToString
Debug.Print ADOShowUserRosterToString(cn)
End Sub
'************ End Example Code ************"
Private Sub ListUusers_Click()
On Error GoTo ListUusers_Click_Err
Dim cn As ADODB.Connection
Dim strDatabasePath As String 'The path the the backend
Set cn = New ADODB.Connection
'strDatabasePath = GetBackendPath("Table1") 'To get the backend path based on a frontend linked table
strDatabasePath = "C:\Users\sneuberg\Desktop\ADO Show User Roster Backend.accdb" 'Explicite path to backend
' Open the connection
With cn
.CursorLocation = adUseServer
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strDatabasePath & ";Mode=Share Deny None;Extended Properties="""""
End With
'Put returned string in the textbox
Me.txtUsers = ADOShowUserRosterToString(cn)
ListUusers_Click_Exit:
Exit Sub
ListUusers_Click_Err:
MsgBox Error$
Resume ListUusers_Click_Exit
End Sub
Private Sub ListUusers_Click()
On Error GoTo ListUusers_Click_Err
Dim cn As ADODB.Connection
Dim strDatabasePath As String 'The path the the backend
Dim strUserList
Set cn = New ADODB.Connection
'strDatabasePath = GetBackendPath("Table1") 'To get the backend path based on a frontend linked table
strDatabasePath = "C:\Users\sneuberg\Desktop\ADO Show User Roster Backend.accdb" 'Explicite path to backend
' Open the connection
With cn
.CursorLocation = adUseServer
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & strDatabasePath & ";Mode=Share Deny None;Extended Properties="""""
End With
'Put returned string in the textbox
Me.txtUsers = ADOShowUserRosterToString(cn)
[COLOR="Red"]Me.txtUsers.SetFocus
[/COLOR]
ListUusers_Click_Exit:
Exit Sub
ListUusers_Click_Err:
MsgBox Error$
Resume ListUusers_Click_Exit
End Sub
Attached is a version of the database I submitted before with the Me.txtUser.SetFocus added and I incorporated Mr arnelgp's code for get the IP address into the list the ADOShowUserRosterToString returns. With information that looks like 192.168.1.63,fe80::a988:d701:4aca:2862 I'm sure you'll know who's using the database right away.
Yes, I didn notice that. The user name, when I run it on my machine says 'LOGIN_NAME:Admin'. I have to test it with others to see what it says for them. I am not admin of anything so I am not sure where it is pulling that from.
But again - thanks for the info. I will work with host name and IP addresses. Perhaps, overtime when I am talking to the users, I will start making note of their computer names and IP address and build that list. Its not a big deal.
Thanks again
