Slow Combo box Population

[Tom Grillot]

I have an Access Project (.adp) with a form that contains a combo box
showing business names and addresses. The recordset for this combo box is a
query that selects all records (3 fields - ID, Name, Address) from a SQL
Server 2000 table with 40,000 records. Populating this combo box takes 15
seconds. The client insists on using a combo box for this purpose, but is
not happy with the 15 seconds it takes to load.

The table's data will not change very often, so I've implemented a
semi-fix - When the user logs on, the table is downloaded then persisted to
the user's pc. Should there be an edit to the data I update both the local,
persisted data and the server table.

Is there any better solution to this? Does the data come down from the
server in pure ANSI or Unicode or whatever, or is there a way to compact it,
send it, and un-compact it?

Thanks for any advice,
Tom

 

[RobFMS]

40K records in a combo box? Ouch! You should create a search screen.

I might suggest creating a dialog form that opens up. The top half provides
the search fields (First Name, Last Name, Address, City, State, Zip Code), a
[Clear] button and a [Search] button. The bottom half is a subform that
shows the results of the criteria specified in the search. A bottom
somewhere else called [Select] can be used to take the selection and push it
to the field (the one that will replace the combo box of 40K records).


--
Rob Mastrostefano

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

 

[Guest]

Tom,

A couple of items that may be of help:

1) Is the search field indexed in the table or is the query running with an
ORDER BY clause? Loading data that is already indeed in the table should be
much faster.
2) The query may be a part of the problem I you are executing it as an SQL
string rather than 'compiled' (saved in your database with a callable name
like "MyQuery" rather than "SELECT from MyTable…. ORDER BY ….."
3) What are you loading into the comboBox? Typically I use two columns one
for the search field and one for an ID field to fetch the info required
information.

In the past I have had to do something similar.
I created an array in memory
Loaded the first couple hundred search Items into the comboBox
Then, with code, searched the array on the KeyUp event as the key strokes
were collected.and repopulated the comboBox with the next 20 records.

Hope this helps…
Marvin

 

[Guest]

Hi Tom,

See the note that I added at the bottom of an article I wrote:

Combo box to find a record
http://www.access.qbuilt.com/html/find_a_record.html

"Author's Note:

A modification can be made in the case that there are many thousands of
records
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/ODC_Cookbo okChapter8.asp)..."


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


:

I have an Access Project (.adp) with a form that contains a combo box
showing business names and addresses. The recordset for this combo box is a
query that selects all records (3 fields - ID, Name, Address) from a SQL
Server 2000 table with 40,000 records. Populating this combo box takes 15
seconds. The client insists on using a combo box for this purpose, but is
not happy with the 15 seconds it takes to load.

The table's data will not change very often, so I've implemented a
semi-fix - When the user logs on, the table is downloaded then persisted to
the user's pc. Should there be an edit to the data I update both the local,
persisted data and the server table.

Is there any better solution to this? Does the data come down from the
server in pure ANSI or Unicode or whatever, or is there a way to compact it,
send it, and un-compact it?

Thanks for any advice,
Tom