Load data from SQL Server into combobox (too slow)

[dealwi8me]

I have a combobox that loads data from a table with 60.000 records. The source table is not local, is linked from SQL Server with ODBC. I save the combobox value into an access linked table.

The problem is that is taking too long to load the data into the combobox. Is there anything i can do to reduce the time needed to load data?

Thank you in advance.

 

[pbaldy]

One way:

http://allenbrowne.com/ser-32.html

 

[dealwi8me]

I will try this... thank you!

 

[dealwi8me]

Does this method works for ODBC connections as well or it's only for access?
Because i tried it and i get the message "ODBC -- call failed".

 

[LPurvis]

Indeed 60,000 rows is massively excessive for a combo (or really any UI list at all!)
By way of a working example - have a look at the "Combo Filtering" example at http://www.databasedevelopment.co.uk/examples.htm.
IMO with that many rows you'll want at the very least two characters before attempting to fill the list.

Upon what line of Allen's code did you receive the error you cite?

 

[pbaldy]

It should work for ODBC as well as Access. As Leigh has already asked, it would help to know the line of code throwing the error.

 

[dealwi8me]

I had in my SQL statement the ORDER BY clause and that was the problem.
I removed it and now it works just fine.

The Combo filtering from http://www.databasedevelopment.co.uk/examples.htm. works as well!

thank you for your help

 

[LPurvis]

Including an Order By clause in the data request isn't unreasonable.
(You're allowed to have the rows in your list controls be ordered - it helps a lot)!

There must have been something about the particular implementation of the ordering.

Glad you're sorted though.