473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
Nov 13 '05 #1
8 6774
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


Nov 13 '05 #2
"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
Nov 13 '05 #3
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.

Nov 13 '05 #4
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.

Nov 13 '05 #5
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.

Nov 13 '05 #6
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.

Nov 13 '05 #7
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.

Nov 13 '05 #8
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.

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Frnak McKenney | last post by:
Back when computer dinosaurs roamed the earth and the precursors to today's Internet were tiny flocks of TDMs living symbiotically with the silicon giants, tracking access to data processing...
2
by: Roy Padgett | last post by:
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...
63
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy...
5
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL...
4
by: Robert | last post by:
Greetings I am assisting a developer with an Access application performance problem and an ODBC timeout. In a nutshell they have a combo box with a drop down that queries a lookup table on a SQL...
3
by: ssb | last post by:
Hello, This may be very elementary, but, need help because I am new to access programming. (1) Say, I have a column EMPLOYEE_NAME. How do I fetch (maybe, cursor ?) the values one by one and...
24
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> ...
2
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with...
6
by: onnodb | last post by:
Hi all, While working on an Access UI to a MySQL database (which should be a reasonable, low-cost, flexible interface to the DB, better than web-based, much less costly than a full-fledged .NET...
7
by: robert.waters | last post by:
I have an Access database frontend linked via ODBC to a large (gigabytes) mysql database. I need to view a large amount of data in a a textbox (variable up to 300K), but I receive a 'there isnt...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.