Who Is Logged On?

  • Thread starter Thread starter EarlCPhillips
  • Start date Start date
E

EarlCPhillips

From "Automatine Microsoft Access With VBA" by Susan Sales Harkins and Mike
Gunderloy published by Que Publishing, 2005, ISBN 0-7897-3244-0, I took a
routine from page 250-1 which publishes a function which gives something to
see who is connected to the database. I cannot get it to work for me. The
code is as follows:
Public Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Public Function ReturnUsers() As String
On Error GoTo Err_ReturnUsers
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
'open connection to database
Set cnn = CurrentProject.Connection
'open schema recordset to grab user metadata
Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _
JET_SCHEMA_USERROSTER)
'return current users
rst.MoveFirst
Do Until rst.EOF
ReturnUsers = rst(0) & ":" & ReturnUsers
rst.MoveNext
Loop

Exit_ReturnUsers:
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
Exit Function
Err_ReturnUsers:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_ReturnUsers
End Function

I built a form that contains a list box. The code to activate the form is:
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim strUsers As String
'populate listbox with current users
strUsers = ReturnUsers
lstCurrentUsers.ControlSource = strUsers
Exit_Form_Load:
Exit Sub
Err_Form_Load: 'handles any error condition
MsgBox "Error number: " & Err.Number & vbCr & Err.Description
Resume Exit_Form_Load
End Sub

What am I doing wrong? Any suggestions?

EarlCPhillips
Ex-Mainframer Learning Access to Make
Local Food Bank Feed Hungry More Efficiently
Harvesters Community Food Network
 
"cannot get it to work" doesn't mean much.

What happens when you try? Do you get an error? If so, what's the error? If
you don't get an error, what happens, and what do you expect to happen
instead?

One thing I question is the use of colon in:

ReturnUsers = rst(0) & ":" & ReturnUsers

AFAIK, you need semi-colon delimitation for list and combo boxes. As well,
have you set the RowSourceType property of lstCurrentUsers to Value List?
 
There are no error messages, the form displays and the list box is empty.
There are no other indications of what is wrong. I changed the list box
property to "Value List" and changed the colon to a semi-colon. I changed
the reference to the list box to "Me.lstCurrentUsers.ControlSource =
strUsers" and recompiled. I put a break in the load subroutine and found
that function ReturnUsers returns only the name of the computer I am using,
but even that is NOT displayed in the list box, but it does show up in the
properties box as the Control Source property.

Any source of help would be appreciated.

EarlCPhillips
Ex-Mainframer Learning Access To Make
Local Food Bank More Efficient
Harvesters Community Food Network
 
I do not have a solution as to why your code does not work, perhaps Douglas
will be able to help you with that, but I do have an alternative. Take alook
at

http://groups.google.ca/group/micro...en&lnk=st&q=ms+access+WhosOn#57dcea37e26fd10d

Alex Dybenko has a relatively simple code to do this.

Also

How to determine who is logged on to a database by using Microsoft Jet
UserRoster in Access 2000
http://support.microsoft.com/?id=198755

How to determine who is logged on to a database by using Microsoft Jet
UserRoster in Access 2002 or in Access 2003
http://support.microsoft.com/?id=285822

Lastly the following poat has numerous good links concerning db user
tracking..
http://groups.google.ca/group/micro...en&lnk=st&q=ms+access+WhosOn#1ea481f2b802de42
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.
 
Just noticed your code in your Form_Load event is incorrect. It's the
RowSource you're trying to set, not the ControlSource:

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim strUsers As String

'populate listbox with current users
strUsers = ReturnUsers
lstCurrentUsers.RowSourceType = "Value List"
lstCurrentUsers.RowSource = strUsers

Exit_Form_Load:
Exit Sub

Err_Form_Load: 'handles any error condition
MsgBox "Error number: " & Err.Number & vbCr & Err.Description
Resume Exit_Form_Load

End Sub
 
Doug's answer gives me the computer names, not the user names. Unless I can
get a cross-reference from the IT manager, this will not work. I will try
some of the other suggestions and see how they work.

EarlCPhillips
 
All Access captures is the computer name and the Access user name. If you
haven't implemented Access User-Level Security, so that the users are
obligated to log into the application, the Access user name is Admin for all
users.
 
I used the code provided by Alex Dybenco and it works. Actually I combined
code from Douglas Steele and Alex. The problem seems to be intractable
because someone here gave everyone Admin authority so each record found lists
"computer name" and "admin" for every user. I am hosed unless someone can
enlighten me on how the powers that be at Harvesters should add authorization
by name rather than adding each user as an administrator. Any help on that
would be appreciated.

EarlCPhillips
ExMainframer Learning Access To Help
Feed The Hungry More Efficiently
Harvesters Community Food Network
 
It's not a case of "adding each user as an administrator". Unless you apply
Access User-Level Security, every user is Admin by default.

Note that adding ULS isn't something to be taken lightly. Check out the
Access Security FAQ at
http://support.microsoft.com/support/access/content/secfaq.asp

Read it thoroughly several times, and work with a copy of your application
(just in case...). Make sure you don't leave out any steps.
 
=?Utf-8?B?RWFybENQaGlsbGlwcw==?=
I used the code provided by Alex Dybenco and it works. Actually I
combined code from Douglas Steele and Alex. The problem seems to
be intractable because someone here gave everyone Admin authority
so each record found lists "computer name" and "admin" for every
user. I am hosed unless someone can enlighten me on how the
powers that be at Harvesters should add authorization by name
rather than adding each user as an administrator. Any help on
that would be appreciated.
Click to expand...

You might try logging your users in a table when they log on,
recording computer name and user logon name. Then you could use the
computer name from the user roster to figure out which users from
that PC is logged on now.
 
I had this same problem and this is how I tackled it:

- I have a list box (LinkedTable) set to dynamically list all linked tables
in my 'Master DB' by having the rowsource set to 'SELECT Name FROM
MSysObjects WHERE Type = 6 ORDER BY Name'
- It's After Update event is set to:
Private Sub LinkedTable_AfterUpdate()

Me.tempMachineName.RowSource = ""
Call WhoIsInLDB 'Ken Snell's code

End Sub
- This populates my tempMachineName Listbox with every Machine Name
currently in the ldb.

- I can then select a Machine Name from that list and hit my 'Find User'
button that has the following code:
Private Sub cmdFind_User_Using_Machinename_Click()
On Error GoTo Err_cmdFind_User_Using_Machinename_Click

Dim TempApp As String
TempApp = "psloggedon -l -x \\" & Me![tempMachineName]
Shell "cmd.exe /k ""psloggedon -l -x \\" & Me![tempMachineName],
vbNormalFocus

Exit_cmdFind_User_Using_Machinename_Clic:
Exit Sub

Err_cmdFind_User_Using_Machinename_Click:
MsgBox Err.Description
Resume Exit_cmdFind_User_Using_Machinename_Clic

End Sub

- This uses the PSLOGGEDON.exe from Sysinternals (from Microsoft's site) and
placed in 'My Documents'.

So short answer...I pick the Linked table from the Linked Table listbox
That populates the tempMachineName listbox with
the current users
I then select the machine name i need and click
the cmdbutton that drives the PSLOGGEDON cmd
You'll get a command window that will show you the
username of that machine name

Hope this helps...has worked great for me.
 
For David Fenton:
David, I tried your Admin.Zip database but it fails when trying to open my
Access 2007 DB. Any updates to the program from ACC2007 ?
 
Back
Top