Results 1 to 9 of 9
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    See list of all connected users

    this is a snippet of code that i don't 'really' need, BUT... continues to annoy me that I can't get it to work
    (so once again... I'm fixated on it)
    in debug.print, all of the information is there; however, putting it into a text string (for inclusion in a msgbox) truncates all but the very first bit of detail

    the line of code that 'seems' to be the problem is:
    gsMsgText = gsMsgText & Trim(rs.Fields(0)) & " " & Trim(rs.Fields(1))
    ...all that is visible is when is: gsMsgText = gsMsgText & Trim(rs.Fields(0))
    ... & " " & Trim(rs.Fields(1)) is NOT visible
    and then only on the first loop


    subsequent loops (if they have entries) do not show at all
    ...trim() seems to have no effect ?

    dazed and confused, but with thanks in advance,
    m.


    Code:
    Sub ShowUserRosterMultipleUsers()
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim i, j As Long
    
        Set cn = CurrentProject.Connection
        Debug.Print CurrentProject.Name
    
        Dim tbl As TableDef, fld
        Set gsDbs = CurrentDb
    
        Set tbl = gsDbs.CreateTableDef("Users")
        Set fld = tbl.CreateField("User", dbText)
    
        ' The user roster is exposed as a provider-specific schema rowset
        ' in the Jet 4.0 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.
        gsMsgText = ""
        Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
            "", rs.Fields(2).Name, rs.Fields(3).Name
        
        If Not rs.EOF And rs.BOF Then
            rs.MoveFirst
        End If
        
        While Not rs.EOF
            Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
            If Len(gsMsgText) > 0 Then
                gsMsgText = gsMsgText & vbCrLf
            End If
            gsMsgText = gsMsgText & Trim(rs.Fields(0)) & "  " & Trim(rs.Fields(1))
            rs.MoveNext
        Wend
        
        'TODO: need to validate that the full list is indeed showing
        If Len(gsMsgText) = 0 Then
            gsMsgText = "no one else has it open"
        End If
        gsMsgResponse = MsgBox(gsMsgText, vbInformation, "LIST OF CURRENT USERS")
    
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How many connected users could there be? Code looks like it should build string.

    Not sure if MsgBox has a limit on number of characters but the length that can be displayed is certainly limited by screen space. MsgBox does not have scrolling so text is lost. My solution was code outputs just so many lines and when user closes MsgBox continues building next group for another MsgBox display. Example:

    Code:
            rsGroup.MoveFirst
            While Not rsGroup.EOF
                strFound = ""
                i = 1
                While i < 51 And Not rsGroup.EOF
                    strFound = strFound & vbCrLf & rsGroup!LABNUM
                    rsGroup.MoveNext
                    i = i + 1
                Wend
                If MsgBox("Samples found, is this list correct?" & vbCrLf & "NO will cancel logout." & vbCrLf & strFound & _
                               IIf(rsGroup.EOF, "", vbCrLf & "MORE..."), IIf(rsGroup.EOF, vbYesNo, vbOKOnly), "GroupSamplesList") = vbNo Then
                    GoTo Exit_proc
                End If
            Wend
    However, I've never even seen OpenSchema and I don't fully understand why it is used. An alternative to MsgBox is a listbox https://stackoverflow.com/questions/...ccess-database
    Last edited by June7; 07-15-2021 at 02:39 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Have a look at the characters output?
    I am the only one on my DB, but the debug shows Acer7720 and Admin, but MsgBox only shows Acer7720 ?

    My debug.print shows ASCII characters of ?

    A-65
    C-67
    E-69
    R-82
    7-55
    7-55
    2-50
    0-48
    -0
    -32
    -32
    A-65
    d-100
    m-109
    i-105
    n-110
    -0
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please try this modified version, I am getting rid of the NULL character Chr(0) that I think breaks the string in the msgbox:
    Code:
    Sub ShowUserRosterMultipleUsers()
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim i As Long, j As Long
    Dim gsDbs As DAO.Database, gsMsgText As String, gsMsgResponse As String
        Set cn = CurrentProject.Connection
        Debug.Print CurrentProject.Name
    
    
        Dim tbl As TableDef, fld
        Set gsDbs = CurrentDb
    
    
        Set tbl = gsDbs.CreateTableDef("Users")
        Set fld = tbl.CreateField("User", dbText)
    
    
        ' The user roster is exposed as a provider-specific schema rowset
        ' in the Jet 4.0 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.
        gsMsgText = ""
        Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
            "", rs.Fields(2).Name, rs.Fields(3).Name
        
        If Not rs.EOF And rs.BOF Then
            rs.MoveFirst
        End If
        
        While Not rs.EOF
            Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
            If Len(gsMsgText) > 0 Then
                gsMsgText = gsMsgText & vbCrLf
            End If
            gsMsgText = gsMsgText & Replace(Trim(rs.Fields(0)), Chr(0), "") & "  " & Replace(Trim(rs.Fields(1)), Chr(0), "")
            rs.MoveNext
        Wend
        
        'TODO: need to validate that the full list is indeed showing
        If Len(gsMsgText) = 0 Then
            gsMsgText = "no one else has it open"
        End If
        gsMsgResponse = MsgBox(gsMsgText, vbInformation, "LIST OF CURRENT USERS")
        'MsgBox gsMsgText, vbInformation, "LIST OF CURRENT USERS"
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    THNX Vlad, that did get the full line of text to display correctly (getting rid of the NULL character Chr(0))

    however, i might now be seeing a different (unexpected of course) problem:

    the linked file that i want to see who has has open is stored in a DropBox folder (and that "might"(?) be part of the disconnect - not certain why... but maybe)
    If i open multiple instances of the program on my desktop... multiple users are displayed
    However, if i open an instance of it on my laptop (linked to the same DropBox file...) that instance does not show (on the desktop instances.)
    On the laptop, the user that would be the lap top does show, but not the users that would be my desktop... Neither shows the other as a user who also has the file open.

    m.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It is definitively because of DropBox; Access files shouldn't be used with any cloud drives like DropBox, OneDrive, Google Drive and the others; they are not meant to allow multiple users like a regular LAN drive does. They actually cache the files locally and update the master one on the cloud drive so you will experience inconsistent data loss depending whose file was the last "synced".

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    ah...

    total sense
    (and luckily, if only by accident, that is not the usual shelf they are stored on!)

    yet again... thnx
    m.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

    Cheers,
    v
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Although you now seem to have a solution, you might be interested in looking at my Check Logged In Users utility.
    This has two variations on the code and can handle users of apps running on the local workstation as well for apps running on 'remote' workstations elsewhere on the LAN
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dividing a list of task between users.
    By trevor in forum Access
    Replies: 11
    Last Post: 08-24-2018, 12:35 PM
  2. looking for database to identify connected users
    By Paintballlovr in forum Access
    Replies: 2
    Last Post: 11-20-2017, 10:14 AM
  3. Select list of Users and details
    By heartbeat_sony in forum Reports
    Replies: 2
    Last Post: 04-18-2017, 02:15 PM
  4. Replies: 1
    Last Post: 03-02-2017, 05:57 PM
  5. Replies: 2
    Last Post: 11-10-2016, 11:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums