Populating an Access combo box with large amount of data causes table lock in SQL Server

[Roy Padgett]

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

[Arno R]

Roy,
You could force them to type 3 letters first
If Len(txtInput) >=3 then
populate your combo
else
Msgbox "Need more input ..." (or maybe you need to do something else here?)
end if

It's always a good idea to limit the amount of data in your comboboxes
Allen Browne has an example here
Check out: http://members.iinet.net.au/~allenbrowne/ser-32.html

--
Hope this helps
Arno R
"Roy Padgett" <ro*@padgett.net> schreef in bericht
news:b0**************************@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

[Rick Brandt]

"Roy Padgett" <ro*@padgett.net> wrote in message
news:b0**************************@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?

If you're using a pass-through anyway you can include a NOLOCK in the SQL
to prevent this.

By default a ListBox or ComboBox will impose a lock on larger RecordSets
until the last row of the data is retrieved. The lock can persist because
only some of the rows are initially cached in. Once you access the last row
the lock is released. With the NOLOCK added the problem is taken care of.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Darryl Kerkeslager]

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT [Customer ID], [Company Name], [contact name],City,
Region FROM Customers ORDER BY [Company Name]"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do stuff

.Close
End With
Set rs = Nothing
------------------------------------

Assuming the Customer_ID is unique, keyword DISTINCT should be redundant.

Unless you have a real need to have the contact_name, city, and region in
the comboBox rowSource, I would take it out, as this will speed up your
comboBox loading data.

Another way of doing this would be to write the query to a table only when
the Customer table changes, then use the table as the rowsource.

Darryl Kerkeslager

"Roy Padgett" <ro*@padgett.net> wrote:

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.

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.

[Roy Padgett]

I know how to make this ADODB connection, but how can you use the
resulting recordset as the rowsource for the combo box?
Thanks,
Roy

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message news:<-6********************@comcast.com>...

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT [Customer ID], [Company Name], [contact name],City,
Region FROM Customers ORDER BY [Company Name]"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do stuff

.Close
End With
Set rs = Nothing
------------------------------------

Assuming the Customer_ID is unique, keyword DISTINCT should be redundant.

Unless you have a real need to have the contact_name, city, and region in
the comboBox rowSource, I would take it out, as this will speed up your
comboBox loading data.

Another way of doing this would be to write the query to a table only when
the Customer table changes, then use the table as the rowsource.

Darryl Kerkeslager

"Roy Padgett" <ro*@padgett.net> wrote:

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.

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.

[Roy Padgett]

I added the WITH (NOLOCK) option. When I run this in Query Analyzer,
I do not get any locks. However, when I run this in Access as a
pass-through query, it does show a SCH-S table lock. Is this normal
or of any concern?

Thanks for the hint,
Roy

"Rick Brandt" <ri*********@hotmail.com> wrote in message news:<2s*************@uni-berlin.de>...

"Roy Padgett" <ro*@padgett.net> wrote in message
news:b0**************************@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?

If you're using a pass-through anyway you can include a NOLOCK in the SQL
to prevent this.

By default a ListBox or ComboBox will impose a lock on larger RecordSets
until the last row of the data is retrieved. The lock can persist because
only some of the rows are initially cached in. Once you access the last row
the lock is released. With the NOLOCK added the problem is taken care of.

[Darryl Kerkeslager]

I don't believe you can do that directly. AFAIK, your choices would be:

1. Output the recordest to a table, and use that as the rowsource. However,
if you're just going to do that, then it makes more sense to preset that
table, then do a statement like:
cnxn.Execute "DELETE * FROM lookup_table"
cnxn.Execute "INSERT INTO lookup_table SELECT [Customer ID], [Company Name],
[contact name],City, Region FROM Customers ORDER BY [Company Name]"

2. Output the recordset as a value list, and use the valuelist as the
rowsourse
(something like):
Do While Not .EOF
s = s & .Fields(0) & ";" & .Fields(1) & ";" & .Fields(2) & ";"
.MoveNext
Loop

I don't really see any benefit in even trying to set the ADODB. recordset as
the rowsource; what would that accomplish?

Perhaps I misunderstand?

Darryl Kerkeslager
"Roy Padgett" <ro*@padgett.net> wrote in message
news:b0**************************@posting.google.c om...

I know how to make this ADODB connection, but how can you use the
resulting recordset as the rowsource for the combo box?
Thanks,
Roy

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message

news:<-6********************@comcast.com>...

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT [Customer ID], [Company Name], [contact name],City, Region FROM Customers ORDER BY [Company Name]"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do stuff

.Close
End With
Set rs = Nothing
------------------------------------

Assuming the Customer_ID is unique, keyword DISTINCT should be redundant.
Unless you have a real need to have the contact_name, city, and region in the comboBox rowSource, I would take it out, as this will speed up your
comboBox loading data.

Another way of doing this would be to write the query to a table only when the Customer table changes, then use the table as the rowsource.

Darryl Kerkeslager

"Roy Padgett" <ro*@padgett.net> wrote:

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.

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.

[Chief Tenaya]

Roy,

In SQL Server, a SCH-S lock is a shared schema lock. This means that SQL
Server has taken a shared lock on the **definition** of the table, not on
the data within the table. (The definition of a table is stored within the
SQL Server system tables).

In other words, SQL Server is preventing any user from changing the
definition of the table schema while you are accessing the data within the
table. All databases have to do this, although different databases may
implement the same logic via different mechanisms.

In short, this is both normal, and should not be of concern to you :-)

Chief Tenaya
"Roy Padgett" <ro*@padgett.net> wrote in message
news:b0**************************@posting.google.c om...

I added the WITH (NOLOCK) option. When I run this in Query Analyzer,
I do not get any locks. However, when I run this in Access as a
pass-through query, it does show a SCH-S table lock. Is this normal
or of any concern?

Thanks for the hint,
Roy

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:<2s*************@uni-berlin.de>...

"Roy Padgett" <ro*@padgett.net> wrote in message
news:b0**************************@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?

If you're using a pass-through anyway you can include a NOLOCK in the SQL
to prevent this.

By default a ListBox or ComboBox will impose a lock on larger RecordSets
until the last row of the data is retrieved. The lock can persist
because
only some of the rows are initially cached in. Once you access the last
row
the lock is released. With the NOLOCK added the problem is taken care
of.

[Roy Padgett]

Thanks for the reply. I couldn't come up with a way to do it either,
and maybe that's because it wasn't necessary.
I knew I could define a ADODB recordset as read only, and with the
locking problem I was having, I thought this might be a way to get
around it. That's the reason I was looking to use ADO.

Roy

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message news:<yr********************@comcast.com>...

I don't believe you can do that directly. AFAIK, your choices would be:

1. Output the recordest to a table, and use that as the rowsource. However,
if you're just going to do that, then it makes more sense to preset that
table, then do a statement like:
cnxn.Execute "DELETE * FROM lookup_table"
cnxn.Execute "INSERT INTO lookup_table SELECT [Customer ID], [Company Name],
[contact name],City, Region FROM Customers ORDER BY [Company Name]"

2. Output the recordset as a value list, and use the valuelist as the
rowsourse
(something like):
Do While Not .EOF
s = s & .Fields(0) & ";" & .Fields(1) & ";" & .Fields(2) & ";"
.MoveNext
Loop

I don't really see any benefit in even trying to set the ADODB. recordset as
the rowsource; what would that accomplish?

Perhaps I misunderstand?

Darryl Kerkeslager
"Roy Padgett" <ro*@padgett.net> wrote in message
news:b0**************************@posting.google.c om...

I know how to make this ADODB connection, but how can you use the
resulting recordset as the rowsource for the combo box?
Thanks,
Roy

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message

news:<-6********************@comcast.com>...

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT [Customer ID], [Company Name], [contact name],City, Region FROM Customers ORDER BY [Company Name]"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText

' Do stuff

.Close
End With
Set rs = Nothing
------------------------------------

Assuming the Customer_ID is unique, keyword DISTINCT should be redundant.
Unless you have a real need to have the contact_name, city, and region in the comboBox rowSource, I would take it out, as this will speed up your
comboBox loading data.

Another way of doing this would be to write the query to a table only when the Customer table changes, then use the table as the rowsource.

Darryl Kerkeslager

"Roy Padgett" <ro*@padgett.net> wrote:

> 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.
>
> 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.