RE: Who is logged in?

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.


"Tatakau" wrote:

> I have an Access 2000 split database on a network. The back-end (data) is on
> a network drive, and individual copies of the front-end (forms & such) are on
> each computer. I would like to make a form for the administrator/manager
> that will show who is currently "logged in" to the database.
>
> And if it makes any difference, the database is currently not secure. At all.
>
> Thanks!
>
> Nick
.