List of users

[teachme]

In a multi user split database environment, is there a way to get a list of users using the application?

I dont have any logon for any users, just a simple split database. If its not too complicated then i would like to incorporate that into the DB else, I think I can live without it.

Thank you

 

[JHB]

..I think I can live without it.

Then live without it.

 

[teachme]

Then live without it.

Ok. No problem

 

[jdraw]

See this free video re Logon form.

 

[teachme]

See this free video re Logon form.

Thank you but I dont think I want to create a logins etc. at this point. I thought there is a much simpler way of finding out who has the application open.

I think if you open the record locking file you can see the name computers that have access open. So I wanted something similar like that.

thx again

 

[jdraw]

see http://accessdatabasetutorial.com/2...g-file-ldb-or-laccdb-within-microsoft-access/

Try opening the laccdb file with Word.

 

[arnelgp]

If its not too complicated then i would like to incorporate that into the DB else, I think I can live without it.

here is the code:

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 ************"

 

[sneuberg]

Mr arnelgp

Thank for the code.

It appears this code needs a reference that's not a default at least not in Access 2013. If you use this code I suggest that you verify that the ADODB reference is enabled. (menu "Tools" > "References...", and verify that "Microsoft ActiveX Data Objects x.x Library" is checked)

 

[NauticalGent]

Then live without it.

Dude...really?

 

[teachme]

here is the code:

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 ************"

Appreciate your code. I actually went live without this but definitely want to have this for my next release. Since I am new to this, would you (or anyone) provide me some info. on how to use the code above.

1. I am assuming that the first part of the code is a module that I would have to create, is that correct?

2. Re: second part of the code. Where does this go? Is this for On Open event of a form or for a command button?

3. Do both codes go in the front end of the database?

4. The "Z:\Tmp.accdb" part in your 2nd code - is that where the back end is located?

Any help with this is greatly appreciated.

Thank you

 

[sneuberg]

1. I am assuming that the first part of the code is a module that I would have to create, is that correct?

Yes, In the attached example database ADO Show User Roster I put that in the User List module I created.

2. Re: second part of the code. Where does this go? Is this for On Open event of a form or for a command button?

In the attached database I put it in the on click event of a button on the frmUser. The code shown below puts the output of the function (the userlist) in a textbox on the form.

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

3. Do both codes go in the front end of the database?

Yes

4. The "Z:\Tmp.accdb" part in your 2nd code - is that where the back end is located?

Yes that would be the backend. I modified the code in the form on click event from the sub test code a bit. In this code I have a variable named strDataBasePath which I concatenate in the the connect open statement in place of where "Z:\Tmp.accdb" was. So you set this to the backend path. I 've included a backend file "ADO Show User Roster Backend" in the zip folder for testing. In just has a single table "Table1". To see this work you will want to relink the frontend to this path end.

I've also included a function in the User List module that gets the backend path if you give it a linked table name from the frontend. This might be useful if your backend gets moved around and you don't want to set it explicitly in this code.

 

[The_Doc_Man]

The question I have is whether you have domain-based logins. If you do, then you can let your users in based on their domain name and don't need a "formalized" login.

This works best if you have a splash screen or switchboard form that stays active until the DB session is about to close. It doesn't matter whether the form stays visible as long as it is open. On the form Load event, capture the Environ("USERNAME") and store that in a table, maybe with a timestamp and, what the heck, just for snorts and giggles store the Environ("COMPUTERNAME") while you are at it. What else is in the table depends on your level of ambition, but you could have a flag that says "In" (TRUE) or "Out" (FALSE). When the Form_Load fires, you do a quickie INSERT INTO for your login table. Remember, I have said NOTHING about dialog boxes. No visible login at all.

When the user does whatever is necessary to shut down a session, have that splash or switchboard Close event (if not canceled) update the record for that username and computer name to show that flag as "Out" (FALSE). Then the solution of who is IN is anyone whose flag is set.

Now, when does this NOT work?
* If you don't use domain-based logins or the domain security is laughably bad - because then you can't trust Environ("USERNAME")
* If your users can get to the ribbon, navigation pane, and other items to bypass the controls imposed by a switchboard-like environment - because in that case you have no guarantee that you can intercept and act on the session shutdown.
* If your network is flaky enough that sometimes users don't have the CHANCE to log out before the network drops out from underneath them.

All of what I just described can be totally invisible to the users so they don't think they are logging in. What is REALLY happening in Windows security terms is that you are allowing a TRUST relation between your application and the domain environment's login security method, thus implementing what the Dept. of Defense calls a "Single Sign-On" environment. In essence, once you are in the domain, you can get to the features of the domain that reside on your system.

 

[teachme]

Yes, In the attached example database ADO Show User Roster I put that in the User List module I created.




In the attached database I put it in the on click event of a button on the frmUser. The code shown below puts the output of the function (the userlist) in a textbox on the form.

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

Yes




Yes that would be the backend. I modified the code in the form on click event from the sub test code a bit. In this code I have a variable named strDataBasePath which I concatenate in the the connect open statement in place of where "Z:\Tmp.accdb" was. So you set this to the backend path. I 've included a backend file "ADO Show User Roster Backend" in the zip folder for testing. In just has a single table "Table1". To see this work you will want to relink the frontend to this path end.

I've also included a function in the User List module that gets the backend path if you give it a linked table name from the frontend. This might be useful if your backend gets moved around and you don't want to set it explicitly in this code.

Thank you - that worked.

I now only have to test this to make sure it pulls the info. on multi users (Computer Names).

A follow up question is: It shows the computer name of the user who is using the database but the computers name in our organization are made up of alphabets and numbers and I cant tell who is in the db just by knowing the 'computer name'.

How do I get that info. without having to ask each user for their computer name? If I ask each user to provide me with their computer name - i really want to go that route.

EDIT: This code is currently pulling the 'Host Name' of user's machine. Can it pull the 'User Name' which is the LAN ID?


Thanks again

 

[arnelgp]

you can convert the host name to IP address, (untested on network):

Public Function fnConvertComputerNameToIP(Host_Name As String) As String
Const wbemFlagReturnImmediately = &H10
Const wbemFlagForwardOnly = &H20
Dim strIPAddress As String
Dim objWMIService As Object
Dim colItems As Object
Dim objItem As Object
Set objWMIService = GetObject("winmgmts:\\" & Host_Name & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled='TRUE'", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)
For Each objItem In colItems
'just the IP
'strIPAddress = objItem.IPAddress(0)
strIPAddress = Join(objItem.IPAddress, ",")
Next
fnConvertComputerNameToIP = strIPAddress
End Function

or if you want the ipaddress, change this:

strIPAddress = Join(objItem.IPAddress, ",")

to:

strIPAddress = objItem.IPAddress(0)

 

[sneuberg]

Sorry. The database I included in my last post only shows the computer name in the textbox until you click in the textbox. I'm not sure why that is but you can fix it by adding the line shown in red below to the ListUusers command button 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
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

 

[sneuberg]

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.

 

[teachme]

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.

Thats Great. Thank you very much. But how would I identify a user by the IP address? Isnt this the same as the host name (computer name)?

Sorry, I must be missing something.

Thank you

 

[sneuberg]

Thats Great. Thank you very much. But how would I identify a user by the IP address? Isnt this the same as the host name (computer name)?

Pretty much. I was being facetious.

Did you notice that my first version didn't show the user name unless you clicked in the textbox. I fixed that (see post 15). Does the inclusion of the username help any?

Where in the system is the information stored that you want displayed?

 

[teachme]

Pretty much. I was being facetious.

Did you notice that my first version didn't show the user name unless you clicked in the textbox. I fixed that (see post 15). Does the inclusion of the username help any?

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

 

[teachme]

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

Update:

IT was able to share a report with me with a list of all host names so I can use that to match the host name with the names. It is a manual process but I am OK with this. This is not something I would have to do on a regular basis.

Thank you