Populating a list box from a recordset quickly

[Caro]

I have a recordset containing several thousand records and am populating a list box for a search with
data.recordset.movenext and list1.additem
Is there a quicker way to do this with a large recordset as it takes a while on the network to populate the list box (up to 25 secs!!)
Thanks

[JHausmann]

Are you hiding the control while populating it? This will consume less time as it doesn't have to do screen refreshes.

[Clunietp]

If you are trying to populate a combobox with a hundred records or more, try rethinking your strategy. If you have to populate that combobox every time each person loads up that form, it will kill the network and, as you have seen, take forever to load. Your network admin will not be very happy with you.

Tom

[Aaron Young]

Other things you can do to optimize the transfer are..
Use an OpenForward Only Recordset and Reduce the Number of Object Branches,(Periods), being referenced, somthing like:

Private Sub Command1_Click()
    Dim oDb As Database
    Dim oRs As Recordset
    Set oDb = OpenDatabase("BIBLIO.MDB")
    Set oRs = oDb.OpenRecordset("SELECT Author FROM Authors", dbOpenForwardOnly)
    While Not oRs.EOF
        'In Some Cases, Referencing by the Field 'Name'
        'Can be faster than the Index No.
        List1.AddItem oRs(0)
        oRs.MoveNext
    Wend
    Set oRs = Nothing
    oDb.Close
    Set oDb = Nothing
End Sub

On my P200 this Populated Over 6,000 Records in a little over 2 Seconds.

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]