Which users are in the database ?

[DanAuber]

Is there a way in Access 2000 to see which users are currently in a database ?

thanks for any help on this

 

[lupins46]

http://support.microsoft.com/?id=198755

 

[DanAuber]

Thanks for that - That works fine as it stands - if it wanted to hook that up to a macro/report - how would I do it ?

 

[lupins46]

WEll, as far as I know you can't assign an ADO recordset as a report source in an mdb file. So you would have to write the values to a table and then use that table as the report's recordsource.
In the code below I have assummed a table 'table11' which contains the 4 fields that are obtained from the user roster. This code works for me, but it is possible that it might depend on the computer names that you use.

Private Sub Report_Open(Cancel As Integer)

ShowUserRosterMultipleUsers

Me.RecordSource = "Select * from table11"
End Sub
Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.recordset
Dim i, j As Long
Dim strsql
Dim AA, BB, CC, DD
Set cn = CurrentProject.Connection

' 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 rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the current database.

' delete any existing entries
strsql = "Delete* from table11"
CurrentDb.Execute strsql, dbFailOnError


While Not rs.EOF
' get rid of problem chars at the end of data values
AA = Left(Trim(rs.Fields(0)), Len(Trim(rs.Fields(0))) - 1)
BB = Left(Trim(rs.Fields(1)), Len(Trim(rs.Fields(1))) - 1)
CC = Trim(rs.Fields(2))
DD = Nz(Trim(rs.Fields(3)), "")


strsql = "Insert into table11 (Computer_Name, Login_Name, Connected, Suspect_state) Values('" & AA & "', '" & BB & "', '" & CC & "', '" & DD & "')"
CurrentDb.Execute strsql, dbFailOnError
rs.MoveNext
Wend
End Sub

 

[severin]

It seems like you guys have everything under control!~)

Here is the program I use (also for 2000):

http://download.microsoft.com/download/access97/utility1/1/win98/en-us/jetutils.exe

It's the one called LDBviewer.exe

 

[gusbrunston]

Hi:
I'll endorse severin's comments. Used LDBviewer for a long time, and it works, AND it's really helpful.
Cheers,

[tt]
Gus Brunston - Access2000/2002(DAO)[/tt]

 

[MichaelRed]

the caveat is to keep in mind that the .LDB file is related to the specific .MDB (or .MDE) file. If you have a split db the situation becomes a bit more interesting.

If (as generally) recommended, the FE of a split db is locally resident (on individual workstations), checking the associated .LDB file in any manner would ONLY show hte user loged in at that work station, so to even come close, you need to reference the BE (data repository) file. The possib;e combinations beyond this get to be even more interesting.




MichaelRed

 

[FancyPrairie]

This function will return a recordset that indicates who is logged on to your database.

'+***************************************************************************************
'*
'*  Sub:        WhosLoggedOn
'*
'*  Author:     FancyPrairie
'*
'*  Date:       December, 2001
'*
'*  Function:   This routine will determine who is logged on the the database specified
'*              by the caller (generally it should be the Workgroup database).
'*
'*              This routine will return the following info in the Recordset passed by
'*              the Caller:
'*
'*              rst.Fields(0).Name = "Computer_Name"    (Char:    Name of the computer)
'*              rst.Fields(1).Name = "LOGIN_NAME"       (Char:    Name of user whos logged in)
'*              rst.Fields(2).Name = "CONNECTED"        (Boolean: True if Connected)
'*              rst.Fields(3).Name = "SUSPECT_STATE"    (Integer: Null if not suspect)
'*
'*  Arguments:  strWorkgroup (string)
'*              ---------------------
'*              This string contains the path (and name) of the database you want to
'*              see who's logged in. Usually you would check the workgroup file.
'*              (Example:  "\\server\ShareName\TheWorkgroup.mdw"
'*
'*              rst (ADODB.Recordset)
'*              ---------------------
'*              This recordset will be returned to the caller.  It will contain the
'*              names of the computers that are logged on to "strWorkgroup" (see the
'*              description of the recordset above).
'*
'*              NOTE:  This routine will create the recordset and populate it.
'*
'*              varSortField (variant - Optional)
'*              ---------------------------------
'*              This variable indicates which field you want the recordset sorted by.
'*              If this argument is not passed, the recordset will not be sorted.  The
'*              possible values for this variable are:
'*                 -1 = Don't sort the data
'*                  0 = Sort by rst.Fields(0)   (Computer_Name)  (DEFAULT)
'*                  1 = Sort by rst.Fields(1)   (Login_Name)
'*                  2 = Sort by rst.Fields(2)   (Connected)
'*                  3 = Sort by rst.Fields(3)   (Suspect)
'*
'*              varAscDesc (variant - Optional)
'*              -------------------------------
'*              Indicates how the data is to be sorted.  The 2 possible values are:
'*                  "ASC"  = Sort Ascending (DEFAULT)
'*                  "DESC" = Sort Descending
'*
'*  Example:    The following is an example of how to call this routine.  The call shown
'*              will return all of the computers logged on to "\\server\ShareName\TheWorkgroup.mdw"
'*              and sorted by "Computer_Name" in Ascending order.
'*
'*                  Dim rst As ADODB.Recordset
'*
'*                  Call WhosLoggedOn("\\server\ShareName\TheWorkgroup.mdw", rst)
'*
'+***************************************************************************************

CODE
Option Compare Database
Option Explicit

Public Sub WhosLoggedOn(strWorkgroup As String, _
                        rst As ADODB.Recordset, _
               Optional varSortField As Variant = 0, _
               Optional varAscDesc As Variant = "Asc")

'********************************
'*  Declaration Specifications  *
'********************************

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    On Error GoTo ErrHandler

'*************************
'*  Open Workgroup file  *
'*************************

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=" & strWorkgroup

    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'*********************************
'*  Create Fields for Recordset  *
'*********************************

    Set rst = New ADODB.Recordset

    rst.Fields.Append rs.Fields(0).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(1).Name, adVarWChar, 32
    rst.Fields.Append rs.Fields(2).Name, adBoolean
    rst.Fields.Append rs.Fields(3).Name, adInteger
    
'*************************************************************************
'*  Loop thru Recordset and add Computer Name, etc. to user's recordset  *
'*************************************************************************

    rst.Open
    
    While Not rs.EOF
        
        rst.AddNew
        If (Not IsNull(rs.Fields(0))) Then rst.Fields(0) = rs.Fields(0)
        If (Not IsNull(rs.Fields(1))) Then rst.Fields(1) = rs.Fields(1)
        If (Not IsNull(rs.Fields(2))) Then rst.Fields(2) = rs.Fields(2)
        If (Not IsNull(rs.Fields(3))) Then rst.Fields(3) = rs.Fields(3)
        rst.Update
        
        rs.MoveNext
    Wend

    If (varSortField <> -1) Then
        rst.Sort = rst.Fields(varSortField).Name & " " & varAscDesc
    End If
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Set rs = Nothing
    Set cn = Nothing

    Exit Sub

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:

    Err.Raise vbObjectError + 20100, "Error occcurred in function WhosLoggedOn", "Error Number: " & Err.number & vbCrLf & vbCrLf & "Error Description: " & Err.Description
        
    Resume ExitProcedure

End Sub

 

[DanAuber]

Hi there lupins46 - I have inserted your code that builds a table - but how do I run a subroutine without having to go into the "Immediate" window.

In other words - what command to I run to build the table and then output it ?

 

[lupins46]

The code I posted run s in the Open event of the report that lists the records. But if you put the code into a different sub you can call it from a button click event if you want.