I have a combo box where users select the customer name and can either
go to the customer's info or open a list of the customer's orders.
The RowSource for the combo box was a simple pass-through query:
SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,
Region FROM Customers ORDER BY Customers.[Company Name];
This was working fine until a couple of weeks ago. Now whenever
someone has the form open, this statement locks the entire Customers
table.
I thought a pass-through query was read-only, so how does this do a
table lock?
I changed the code to an unbound rowsource that asks for input of the
first few characters first, then uses this SQL statement as the
rowsource:
SELECT [Customer ID], [Company Name], [contact name],City, Region From
dbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY
[Company Name];
This helps, but if someone types only one letter, it could still be
pulling a few thousand records and cause a table lock.
What is the best way to populate a large combo box? I have too much
data for the ADODB recordset to use the .AddItem method
I was trying to figure out how to use an ADODB connection, so that I
can make it read-only to eliminate the locking, but I'm striking out
on my own.
Any ideas would be appreciated.
Roy
(Using Access 2003 MDB with SQL Server 2000 back end)