Results 1 to 7 of 7
  1. #1
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7

    Populate listbox with DAO Recordset

    Hi,



    Because many users use my database, I would like to optimize my program. I would like users to connect to the database for a moment, receive certain records (listbox), and then the connection would close. I did it with an ADO code, but I can not deal with DAO, which works faster. In the DAO code, the listbox populates, only when I don't close the recordset / connection. Can it be changed?

    DAO
    Code:
        Dim db As DAO.Database, Rst As DAO.Recordset, strSQL$    Set db = DAO.OpenDatabase(StrDb, dbOpenSnapshot)
        Set Rst = db.OpenRecordset(strSQL)
        Set Me.listBoxTest.Recordset = Rst
        Rst.Close
        db.Close
    ADO - works
    Code:
        Const strConnect$ = "Provider=Microsoft.ACE.OLEDB.12.0;" & _                    "Data Source=" & StrDb & ";" & _
                        "Jet OLEDB:Database Password=" & passDb & ";"
        Dim strSQL$
        Dim Conn As ADODB.Connection
        Dim Rst As ADODB.Recordset
        Set Rst = New ADODB.Recordset
        Rst.CursorType = adOpenStatic
        Set Conn = CreateObject("ADODB.Connection")
        Conn.CursorLocation = adUseClient
        Conn.Open strConnect
        Rst.Open strSQL, Conn, adOpenStatic, adLockReadOnly
        Set Me.listBoxTest.Recordset = Rst
        Rst.Close
        db.Close

    andrews
    --
    Sorry form my English

  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
    The ADO code does not close connection. It closes db object which you don't even show declared and set. However, even if not explicitly closed and set to nothing, objects should not persist when the procedure ends. Tested your code but added line Conn.Close and combobox does still work. Interesting. And DAO does not and don't see how it can.

    If must use DAO then expect will have to set combobox for ValueList and build the list by looping recordset. Will this be faster than ADO?
    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
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I believe access is better when you maintain a persistent connection to the back end so I assume your doing it somewhere in your app.

    When I use a recordset with a list box I usually use a value list list box and loop through the recordset and either return a string to use as a rowsource or use the .AddItem method.

    something along the lines of

    for the .AddItem method
    Code:
    Private Sub FillList()
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSql As String
    
    
        strSql = "select * from  SomeTable"
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSql)
    
    
        If rs.BOF And rs.EOF Then
            GoTo MyExit
        End If
    
    
        Do Until rs.EOF
    
    
            SomeListbox.AddItem rs!MyPrimaryKey & ";" & rs!SomeField
    
    
            rs.MoveNext
    
    
        Loop
    
    
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    
    End Sub
    for the rowsource method
    Code:
    Private Function FillList2() As String
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSql As String
        Dim strOut As String
    
    
        strSql = "select * from  SomeTable"
    
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strSql)
    
    
        If rs.BOF And rs.EOF Then
             strOut = ""
            GoTo MyExit
        End If
    
    
        Do Until rs.EOF
    
    
            strOut = strOut & rs!MyPrimaryKey & ";" & rs!SomeField & ";"
    
    
            rs.MoveNext
    
    
        Loop
        
        If Nz(strOut, "") <> "" Then
        
        strOut = Left(strOut, Len(strOut) - 1)
        
        End If
        
        FillList2 = strOut
    
    
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
    
    End Function
    call it like MyListBox.rowsource = FillList2

    be sure to set your column counts and widths.

  4. #4
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7
    Thanks for the answers.


    I use linked tables and related forms, but when about 15 users connect to the backend database, the application is slowed down. I have read on the forums that applying DAO or ADO so that user's application only connects when it needs it, should help with these problems. So I'm looking for the best solution.
    Adding records to the listbox in the loop is a solution, but it is also quite slow with several thousand records.
    What are your experiences with the work of many users on the basis of access? Can I ask for some tips?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Adding records to the listbox in the loop is a solution, but it is also quite slow with several thousand records.
    Thats an awful lotta records for a listbox and very likely very user unfriendly.
    You may want cascading Combo\list boxes to narrow the list down.

  7. #7
    andrews is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2019
    Posts
    7
    Hey,


    I used your suggestions...
    I created a database variable that is open as long as the application is running.
    This makes the connection to the backend database (DAO) to be faster.
    I have limited the number of records displayed on the list only to those needed for the user - about 50 records.
    List data is loaded using the method record source.
    For one continuous form I used ADO, because I found that closing the connection does not delete data from the form - just like in the listbox.
    Currently, the application works well but I have not made it available to users. I will write if it changes.


    Thank you for your help.

    andrews



    --
    Sorry form my English

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

Similar Threads

  1. Populate Listbox
    By dccjr3927 in forum Programming
    Replies: 2
    Last Post: 01-15-2019, 05:19 PM
  2. Replies: 13
    Last Post: 09-10-2015, 03:37 PM
  3. Populate listbox from Recordset
    By PATRICK in forum Access
    Replies: 8
    Last Post: 10-28-2013, 11:00 PM
  4. Replies: 10
    Last Post: 06-18-2013, 02:00 PM
  5. Populate a Combo Box from a RecordSet
    By dreamnauta in forum Programming
    Replies: 1
    Last Post: 01-27-2012, 03:18 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