Populating Listbox from Recordset. Poor performance.

[tehNellie]

I have an adodb recordset populated by a SQL server stored procedure. I want to put the contents of that recordset into a listbox.

So far so good, but if the SP returns more than approx 150 rows, populating the listbox causes the performance to nose dive.

To populate the listbox I'm using:

    Do While Not rsRoles.EOF
      Me.lstRoles.AddItem rsRoles![role]
      rsRoles.MoveNext
    Loop

Cursors are client side and the recordset performance the same whether running connected or disconnected.

In the end what I've ended up doing at the moment is prompting the user when a "large" volume (ie more than 150 records) are returned, creating a local temporary table in access, populating that with the results and resetting the list box to use the local table as a rowsource rather than populating it as a valuelist from the recordset.

when I add each record to the list box to create a single row (just testing things) there's no problem regardless of the size of the recordset ie

Me.lstRoles.RowSource = Me.lstRoles.RowSource & ";" & rsRoles![role]

Am I missing something obvious here?

 

[Pat Hartman]

When I work with SQL Server or other "big" databases, I always use linked tables and only in rare cases do I need stored procedures or views so I don't even know if what I am going to suggest makes sense. I would create a pass-through query to call the stored procedure and set the pass-through query as the combo's RowSource. This should work as long as the sp returns only a single recordset.