How to Generate a User List

For this Tip, we will show you an extremely handy, multi-user, feature of Jet that allows you to manage Users more effectively. You can create a special, provider-specific Recordset in ADO that supplies information about the current Users in the Database. This is accomplished by using the OpenSchema() Method of the Connection Object, which will fill a recordset with varied sets of different Database Schema information. The output of this OpenSchema() Method will be written to a List Box (lstUsers) with the pre-defined headings of Computer, UserName, Connected?, and Suspect?.

I intentionally decided to display this Tip at this specific point in time because it is intimately related to the prior Tip, Passive Shutdown. They are both extremely helpful, multi-user, features that Jet 4 exposes, and they will greatly assist in the implementation of certain administrative tasks such as: Updates, Backups, etc. There will be certain instances when you, as the administrator of a multi-user Database, will want to be 100% sure that no Users are currently logged on to your back end database. Through the use of Passive Shutdown, when no additional Users will be allowed to log on, and the User List, which will display any currently logged on Users, you will be 100% guaranteed that no currently logged on individual will interfere with a critical process.

The code segment below will demonstrate how you can display this User List in a List Box on a Form. There are ample comments scattered through the code, but if you need any further explanation on any specific area, please feel free to ask.
OUTPUT:

re: How to Generate a User List

Does this only list users who are using the same front end MDB? Or will it list users who are using separate front end MDBs linked to the same backend MDB?

re: How to Generate a User List

In a Multiuser Environment, it will generate a list of Current Users who are logged on to the Back End Database from separate Front End MDBs linked to the same backend. This would be the most practical application.

re: How to Generate a User List

Thank you for supplying the code above, can you give me some more information regarding the basics of setting it up. Do you need to place the code into a module? from which a list box named "1stUsers" placed on a form displays data from another table with the pre-defined headings of Computer, UserName, Connected?, and Suspect? Thanks you for any assistance you may be able to provide.

re: How to Generate a User List

This is exactly how I would incorporate this functionality in my Database:

1. Create a Form dedicated solely to the display of this User List. It should not serve any other function.
2. Create a Private Sub Procedure in your Form and name it GenerateUserList. Copy and Paste the User List code to this Procedure as in:
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub GenerateUserList()
   2. 'The User List Schema information requires this magic number. For anyone
   3. 'who may be interested, this number is called a GUID or Globally Unique
   4. 'Identifier - sorry for digressing
   5. Const conUsers = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
   6.  
   7. Dim cnn As ADODB.Connection, fld As ADODB.Field, strUser As String
   8. Dim rst As ADODB.Recordset, intUser As Integer, varValue As Variant
   9.  
   10. Set cnn = CurrentProject.Connection
   11. Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, SchemaID:=conUsers)
   12.  
   13. 'Set List Box Heading
   14. strUser = "Computer;UserName;Connected?;Suspect?"
   15.  
   16. With rst    'fills Recordset (rst) with User List data
   17.   Do Until .EOF
   18.     intUser = intUser + 1
   19.       For Each fld In .Fields
   20.         varValue = fld.Value
   21.           'Some of the return values are Null-Terminated Strings, if
   22.           'so strip them off
   23.           If InStr(varValue, vbNullChar) > 0 Then
   24.             varValue = Left(varValue, InStr(varValue, vbNullChar) - 1)
   25.           End If
   26.           strUser = strUser & ";" & varValue
   27.       Next
   28.         .MoveNext
   29.   Loop
   30. End With
   31.  
   32. Me!txtTotalNumOfUsers = intUser        'Total # of Users
   33.  
   34. 'Set up List Box Parameters
   35. Me!lstUsers.ColumnCount = 4
   36. Me!lstUsers.RowSourceType = "Value List"
   37. Me!lstUsers.ColumnHeads = False
   38.   lstUsers.RowSource = strUser       'populate the List Box
   39.  
   40. 'Routine cleanup chores
   41. Set fld = Nothing
   42. Set rst = Nothing
   43. Set cnn = Nothing
   44. End Sub
3. Create a List Box on this Form and name it lstUsers. Set no properties for this List Box, they will be set programmatically. It's Name, however, must be lstUsers.
4. Create a Text Box on the Form and name it txtTotalNumOfUsers.
5. Set the Form's Timer Interval to 10000 (10 seconds). This can be readjusted later if you so desire.
6. Place the following code in the Form's Timer() Event:
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub Form_Timer()
   2.   Call GenerateUserList
   3. End Sub
   4.  
7. Place the following code in the Form's Open() Event:
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub Form_Open(Cancel As Integer)
   2.   Call GenerateUserList
   3. End Sub
8. When you initially Open the Form the List Box will be populate with all the Users currently logged on to the Database and this List will be Refreshed every 10 seconds (Timer Interval = 10000 / Timer() Event call to GenerateUserList).
9. If you have any further questions whatsoever on anything that I have outlined, please feel free to ask.

re: How to Generate a User List

I was having trouble implementing this on my front end but then I figured it out! It has to be on the back end for this to work.

Thanks,
Chris

re: How to Generate a User List

This works great, thanks. But, why are all the UserNames "Admin"?

re: How to Generate a User List

If you do not have a Security System in place all Users are defined as Admin with no Password. I'm assuming that this is your case.

re: How to Generate a User List

Yes, that is the case, thanks for the help. At least I can get the computer name.

re: How to Generate a User List

This works great, except that in addition to getting "Admin" for all the UserNames, I get something like "C05-4235" under Computer.

We use Novell NetWare Services to log in to our computers. Is there anyway that I can get it to display the Novell user name in the list box?

Thanks!

re: How to Generate a User List

Hi there,

i created a macro wher in it pulls the data from an excel sheet (a report).

i got the data, but the problem is the output is scattered..

can anyone gimme a code to get the outputs aligned?

thanks

re: How to Generate a User List

Don't know if you still need this, but my project is the same, and with a bit of fiddling, I've got it sort of working. Huge thanks to ADezii for the original code.

The bits in bold are additional

It does leave it slightly messy with the Admin user still being displayed, but that doesn't matter for my purposes, so I've left it in.

The GetUserName() function I found elsewhere on the web, but not sure where, so can't acknowledge the original creator, however, it's not mine.

Hope that helps.