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 8 6812
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.ne t> schreef in bericht
news:b0******** *************** ***@posting.goo gle.com... 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
"Roy Padgett" <ro*@padgett.ne t> wrote in message
news:b0******** *************** ***@posting.goo gle.com... 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
Dim cnxn As ADODB.Connectio n
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, adOpenForwardOn ly, 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.ne t> 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.
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*********@co mcast.net> wrote in message news:<-6************** ******@comcast. com>... Dim cnxn As ADODB.Connectio n 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, adOpenForwardOn ly, 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.ne t> 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.
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*********@ho tmail.com> wrote in message news:<2s******* ******@uni-berlin.de>... "Roy Padgett" <ro*@padgett.ne t> wrote in message news:b0******** *************** ***@posting.goo gle.com... 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 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.ne t> wrote in message
news:b0******** *************** ***@posting.goo gle.com... 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*********@co mcast.net> wrote in message
news:<-6************** ******@comcast. com>... Dim cnxn As ADODB.Connectio n 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, adOpenForwardOn ly, 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.ne t> 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,
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.ne t> wrote in message
news:b0******** *************** ***@posting.goo gle.com... 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*********@ho tmail.com> wrote in message news:<2s******* ******@uni-berlin.de>... "Roy Padgett" <ro*@padgett.ne t> wrote in message news:b0******** *************** ***@posting.goo gle.com... > 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.
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*********@co mcast.net> wrote in message news:<yr******* *************@c omcast.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.ne t> wrote in message news:b0******** *************** ***@posting.goo gle.com... 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*********@co mcast.net> wrote in message news:<-6************** ******@comcast. com>... Dim cnxn As ADODB.Connectio n 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, adOpenForwardOn ly, 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.ne t> 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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 resources was
much simpler: you logged in with a userID and password, and when
you were done you ended your session by logging out (or occasionally
by being disconnected). Connection time was easy to measure, and it
made sense to both the customer...
|
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 query:
SELECT DISTINCT , , ,City,
Region FROM Customers ORDER BY Customers.;
This was working fine until a couple of weeks ago. Now whenever
someone has the form open, this statement locks the entire Customers
table.
|
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 in Visual Studio to create reports and labels as it's in
Access?`
The advantage of VS.net is that not every user needs Access, right? And
that would eliminate the Access version problem as well I guess.
|
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 2000. Of
course there were no modifications made to the queries and they
noticed significant performance issues. They recently upgraded the
application to Access XP expecting the newer version to provide
performance benefits and now queries take...
|
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 Server database over ODBC and returns 30,000+ records each time it is accessed. By using a stored procedure to view the locks on the tables in the DB we suspect that a locking issue or a dead lock occurs which is causing the timeout. Once the user...
| |
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 populate a combo box with these names.
(this way, I can display all the EMPLOYEE_NAME values)
(2) In general, can I do additional processing on column values from
|
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>
But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???
Thanks
|
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 this. Also - the
citrix folks do not want us to keep the FE in Access as the queries and
other activities consume a lot of power. The users will be in 3
different offices across the globe all accessing the 1 Oracle DB in
Citrix.
Does anyone have...
|
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 app or so.... is
it?), I tried measuring the bandwith consumed by the
Access/MyODBC/MySQL link, which came out to be, er, quite high.
I fancied it would be interesting to look at the queries Access throws
at MySQL through the ODBC link, so I...
|
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 enough memory' error whenever I
scroll past N number of bytes in a textbox that has been filled with a
lot of data. I am not sure what N is, but for a large chunk of data
it occurs at about the halfway scroll, and smaller chunks might not
throw...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |