Hi, Nick.
I would like to make a form for the administrator/manager
that will show who is currently "logged in" to the database.
One may use the Jet User Roster for checking users logged into the database.
One common usage is to display this information in the Immediate Window.
Please see the following Web page for sample code:
http://support.microsoft.com/default.aspx?id=285822
If you'd like to display the information in a list box on the form instead,
then set a Reference to the ADO library, create a list box with four columns,
name it lstRoster, and create a button, name it UserRosterBtn, then paste the
following code in the form's code module:
Private Sub UserRosterBtn_Click()
On Error GoTo ErrHandler
Dim cnxn As New ADODB.Connection
Dim recSet As New ADODB.Recordset
Dim sCompName As String
Dim sLoginName As String
Dim sList As String
Dim sValue As String
Dim idx As Long
Dim pos As Long
cnxn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxn.Open "Data Source=T:\Data\MyData.mdb"
' 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 recSet = cnxn.OpenSchema(adSchemaProviderSpecific, , _
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")
'-------------------------------------------------------
' Output the list of all users in the database.
'-------------------------------------------------------
'-------------------------------------------------------
' Get headers for each column in list box.
'-------------------------------------------------------
sList = recSet.Fields(0).Name & ";" & recSet.Fields(1).Name & _
";" & recSet.Fields(2).Name & ";" & recSet.Fields(3).Name
While Not recSet.EOF
'-------------------------------------------------------
' Determine the computer name from Unicode.
'-------------------------------------------------------
sValue = Left(recSet.Fields(0).Value, Len(recSet.Fields(0).Value))
For idx = 1 To Len(sValue)
If (Asc(Mid$(sValue, idx, 1)) = 0) Then
pos = idx
Exit For
End If
Next idx
sCompName = Left(recSet.Fields(0).Value, (pos - 1))
pos = 0
' Reset.
'-------------------------------------------------------
' Determine the login name from Unicode.
'-------------------------------------------------------
sValue = Left(recSet.Fields(1).Value, Len(recSet.Fields(1).Value))
For idx = 1 To Len(sValue)
If (Asc(Mid$(sValue, idx, 1)) = 0) Then
pos = idx
Exit For
End If
Next idx
sLoginName = Left(recSet.Fields(1), (pos - 1))
'-------------------------------------------------------
' Retrieve values for entire row in list box.
'-------------------------------------------------------
sList = sList & ";" & sCompName & ";" & sLoginName & _
";" & recSet.Fields(2) & ";" & _
IIf(IsNull(recSet.Fields(3)), "Null", recSet.Fields(3))
recSet.MoveNext
Wend
Me!lstRoster.RowSource = sList
Me!lstRoster.Requery
CleanUp:
Set recSet = Nothing
Set cnxn = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in UserRosterBtn_Click( ) in " & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description, _
vbExclamation + vbOKOnly, "Error!"
Err.Clear
GoTo CleanUp
End Sub
.. . . and replace T:\Data\MyData.mdb with the path to your database on the
networked server (UNC is preferred), save and compile. At the click of a
button, the user can display all of the users logged into the database on the
server.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
atabase Password=MyDbPassword"