Results 1 to 3 of 3

Thread: User Log

  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: User Log

    First off, let me admit I am not the greatest at this sort of thing, so my apologies for any galactically stupid questions I pose. I got the following code from Microsoft's Website.
    PHP Code:
    Sub ShowUserRosterMultipleUsers()
        
    Dim cn As New ADODB.Connection
        Dim rs 
    As New ADODB.Recordset
        Dim i
    As Long

        Set cn 
    CurrentProject.Connection

        
    ' 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.

        
    Debug.Print rs.Fields(0).Name""rs.Fields(1).Name_
        
    ""rs.Fields(2).Namers.Fields(3).Name

        
    While Not rs.EOF
            Debug
    .Print rs.Fields(0), rs.Fields(1), _
            rs
    .Fields(2), rs.Fields(3)
            
    rs.MoveNext
        Wend

    End Sub 
    This produces a list of users in the Immediate Window in VB. Does anyone know how to output this information to a text box on a form (or something of the like)? Thank you to anyone who can help.
    Me.Geek = True

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    For anyone else who has this problem, or is just interested, check out Microsoft's Site for their original version of the code, then see below for (what I believe to be) a better solution. The only problem I have noticed is that you can't run this code when someone has the database open with shift-bypass, so I put in some error-handling to at least address this problem.

    1. Create a form called "Frm_Userlog", with a textbox on it called "TxtBox_UserLog".

    2. On an event (I use the form open AND a timer at 5 seconds), use the following code:
    PHP Code:
    On Error GoTo Err_Form_Open

    Me
    .TxtBox_UserLog ShowUserRosterMultipleUsers()

    Err_Form_Open:
        
    DoCmd.Hourglass False
        DoCmd
    .Echo True
        Msg 
    "Error # " Str(Err.Number) & " was generated by " Err.Source _
            
    vbNewLine vbNewLine "Description: " Err.Description _
            
    vbNewLine vbNewLine "Please contact the database administrator."
        
    msgbox MsgvbMsgBoxHelpButton"Error"Err.HelpFileErr.HelpContext
        DoCmd
    .Close acForm"Frm_Userlog"acSaveNo 
    3. Create a new module, call it "ShowUsers", and insert the following code.
    PHP Code:
    Function ShowUserRosterMultipleUsers() As String
         
        Dim cn 
    As New ADODB.Connection
        Dim cn2 
    As New ADODB.Connection
        Dim rs 
    As New ADODB.Recordset
        Dim i
    As Long

        cn
    .Provider "Microsoft.Jet.OLEDB.4.0"
        
    cn.Open "Data Source=C:\Change\This\Directory\Name.mdb"

        
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        
    "Data Source=C:\Change\This\Directory\Name.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 rs = cn.OpenSchema(adSchemaProviderSpecific, _
        , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

        '
    Output the list of all users in the current database.
        
    ShowUserRosterMultipleUsers rs.Fields(0).Name "," rs.Fields(1).Name "," rs.Fields(2).Name "," rs.Fields(3).Name vbCrLf
        
    While Not rs.EOF
            ShowUserRosterMultipleUsers 
    ShowUserRosterMultipleUsers rs.Fields(0) & "," rs.Fields(1) & "," rs.Fields(2) & "," rs.Fields(3) & vbCrLf
            rs
    .MoveNext
        Wend
        
        
    'Need to type in ShowUserRosterMultipleUsers to show list of users
       rs.Close
       Set rs = Nothing
        
    End Function 
    One cautionary: make sure to change the path of the database in the code (it appears twice, I'll let you play Where's Waldo to find them).
    Last edited by nckdryr; 01-03-07 at 15:38.
    Me.Geek = True

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    replace the debug.print with the name of the text object

    But we can make it easyer

    replace the sub with function

    so Sub ShowUserRosterMultipleUsers() comes Function ShowUserRosterMultipleUsers()

    and

    End Sub to End Function


    replace the
    Debug.Print rs.Fields(0).Name, ...... rest of line
    with
    ShowUserRosterMultipleUsers = rs.Fields(0).Name & " " & rs.Fields(1).Name & _
    " " & rs.Fields(2).Name & rs.Fields(3).Name & vbNewLine

    same with teh other debug.print

    know in a form put a textbox in the Control source put =ShowUserRosterMultipleUsers()
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •