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)

"Roy Padgett" <roy@padgett.net> wrote in message
news:b0ae64bc.0410040946.32bd1ada@posting.google.c om...
>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)

Any time you issue a query with no WHERE clause (your first query), you're
asking SQL Server to scan the whole table, so the only thing it can do is
take some sort of exclusive lock to prevent data changing as it's reading
the table. Adding a WHERE clause allows SQL Server to use indexes to reduce
the number of rows it has to read, but as you say, if the number of rows is
large, or if the indexes aren't useful, then it may scan the table anyway.

It would be useful to know what the table structure looks like and what the
current indexes are, ie. CREATE TABLE and CREATE INDEX scripts for the
table. If the Customers table is mainly used for queries like the ones
above, then you could consider a clustered index on [Company Name], so that
the data is already in the order you need.

You also say that the table is locked while "the form is open" - this
suggests that your front end may be starting a transaction, then not
committing it until the form is closed. From the back end, you can use DBCC
OPENTRAN to is there's an open transaction, and DBCC INPUTBUFFER,
fn_get_sql() or Profiler to see what the SPID is doing. If there is an open
transaction, you'll need to look into what exactly Access is doing - you
might want to post in an Access group also, as this may be a common issue in
the Access world.

Simon

Try looking at the "WITH NOLOCK" syntax of SQL Server, I don't remember it
off the top of my head.
This will make sure that no locks are held.

Oscar...

"Roy Padgett" <roy@padgett.net> wrote in message
news:b0ae64bc.0410040946.32bd1ada@posting.google.c om...
> 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)