I just got paid to solve this problem for one client, so I might be impeding
future business opportunities by sharing this secret, but a rising tide floats
all boats, so... I've seen this mysterious problem now in many Access C/S
applications. Usually some random GUI or workflow changes make the problem,
but for at least one of my clients, the problem was chronic.
You have an Access form bound to a linked SQL Server table. You try to save a
record from Access, and it hangs, possibly timing out with an error
eventually, or possibly until you kill Access or kill the connection from the
server-side (regardless of the timeout setting in Access). During this
lock-up, there is one SQL Server process blocking another - both intiated from
the same client workstation. The problem is usually intermittent, and hard to
reproduce.
Of course, if the 2 processes were blocking each other on the server-side, SQL
Server would detect that as a deadlock, and pick one of them to fail, but in
this case, it turns out we have process A (a SELECT query returning rows to
Access) blocking process B (an update) on the server, and Access hanging
waiting for process B before it will continue reading rows from process A,
thus allowing A to complete and stop blocking process B.
It turns out that process A is always the query for a combo box or list box,
and the answer turns out to be to always force combo and list boxes to
populate completely by reading the ListCount property immedately on form load,
and after every time a combo box is requeried or has its rowsource changed.
Presto - problem solved.
14  2156 
Steve Jorgensen wrote: I just got paid to solve this problem for one client, so I might be
impeding future business opportunities by sharing this secret, but a
rising tide floats all boats, so... I've seen this mysterious
problem now in many Access C/S applications. Usually some random GUI
or workflow changes make the problem, but for at least one of my
clients, the problem was chronic.
You have an Access form bound to a linked SQL Server table. You try
to save a record from Access, and it hangs, possibly timing out with
an error eventually, or possibly until you kill Access or kill the
connection from the server-side (regardless of the timeout setting in
Access). During this lock-up, there is one SQL Server process
blocking another - both intiated from the same client workstation.
The problem is usually intermittent, and hard to reproduce.
Of course, if the 2 processes were blocking each other on the
server-side, SQL Server would detect that as a deadlock, and pick one
of them to fail, but in this case, it turns out we have process A (a
SELECT query returning rows to Access) blocking process B (an update)
on the server, and Access hanging waiting for process B before it
will continue reading rows from process A, thus allowing A to
complete and stop blocking process B.
It turns out that process A is always the query for a combo box or
list box, and the answer turns out to be to always force combo and
list boxes to populate completely by reading the ListCount property
immedately on form load, and after every time a combo box is
requeried or has its rowsource changed. Presto - problem solved.
You can also specify NO LOCK if using a pass-through for the List/Combo and
that will also prevent the problem.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Tue, 15 Feb 2005 15:44:15 -0600, "Rick Brandt" <ri*********@hotmail.com>
wrote: Steve Jorgensen wrote: I just got paid to solve this problem for one client, so I might be
impeding future business opportunities by sharing this secret, but a
rising tide floats all boats, so... I've seen this mysterious
problem now in many Access C/S applications. Usually some random GUI
or workflow changes make the problem, but for at least one of my
clients, the problem was chronic.
You have an Access form bound to a linked SQL Server table. You try
to save a record from Access, and it hangs, possibly timing out with
an error eventually, or possibly until you kill Access or kill the
connection from the server-side (regardless of the timeout setting in
Access). During this lock-up, there is one SQL Server process
blocking another - both intiated from the same client workstation.
The problem is usually intermittent, and hard to reproduce.
Of course, if the 2 processes were blocking each other on the
server-side, SQL Server would detect that as a deadlock, and pick one
of them to fail, but in this case, it turns out we have process A (a
SELECT query returning rows to Access) blocking process B (an update)
on the server, and Access hanging waiting for process B before it
will continue reading rows from process A, thus allowing A to
complete and stop blocking process B.
It turns out that process A is always the query for a combo box or
list box, and the answer turns out to be to always force combo and
list boxes to populate completely by reading the ListCount property
immedately on form load, and after every time a combo box is
requeried or has its rowsource changed. Presto - problem solved.
You can also specify NO LOCK if using a pass-through for the List/Combo and
that will also prevent the problem.
Where do you specify NO LOCK? Specifying "No Locks" on the form definitely
does not help.
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:37*************@individual.net... Steve Jorgensen wrote:
You can also specify NO LOCK if using a pass-through for the List/Combo
and
that will also prevent the problem.
I can see two potential problems with using a NO LOCK hint. First, it's only
supported by SQL Server. Second, the resultset becomes read-only - so you
can't re-use the same procedure for a records-updating function later on.
Although it involves writing more code i think a better way might be to use
callback functions and a forward-only recordset to populate lists and combo
boxes.
On Tue, 15 Feb 2005 19:30:36 -0500, "John Winterbottom" <as******@hotmail.com>
wrote: "Rick Brandt" <ri*********@hotmail.com> wrote in message
news:37*************@individual.net... Steve Jorgensen wrote:
You can also specify NO LOCK if using a pass-through for the List/Combo
and
that will also prevent the problem.
I can see two potential problems with using a NO LOCK hint. First, it's only
supported by SQL Server. Second, the resultset becomes read-only - so you
can't re-use the same procedure for a records-updating function later on.
Ah - the lock hint. That may be OK because I've never seen this symptom with
other back-ends (only tried PostgreSQL), and the lock hint would be used in
stored procedures, so the results are read-only anyway with an MDB front-end.
Although it involves writing more code i think a better way might be to use
callback functions and a forward-only recordset to populate lists and combo
boxes.
Hmm - I had thought of that, and dismissed it as overengineering since getting
the ListCount works. Thinking more about it, though, I guess it doesn't
require any support code in the forms like the ListCount approach does, and is
not prone to code omission later.
Steve Jorgensen wrote: Although it involves writing more code i think a better way might be to use
callback functions and a forward-only recordset to populate lists and combo
boxes.
I find callback functions painstakingly slow to populate a combo box,
the only time I've used them in the past is to poplate lists of tables
or forms in the database, it may be just that. But the thing is when
using the type down facility it appears to be running all that callback
code again and again. I've changed all those combos to now use a temp
table in the front end and populate that on form load, much improved.
Hmm - I had thought of that, and dismissed it as overengineering since getting
the ListCount works. Thinking more about it, though, I guess it doesn't
require any support code in the forms like the ListCount approach does, and is
not prone to code omission later.
I generally call a common function in the open event of every form,
something that then calls the ScaleForm from ADH so I'd put a common
function there to loop and do all combos. Only problem would be when
requerying later on.
--
This sig left intentionally blank
On Wed, 16 Feb 2005 08:17:48 +0000, Trevor Best <no****@besty.org.uk> wrote:
.... Hmm - I had thought of that, and dismissed it as overengineering since getting
the ListCount works. Thinking more about it, though, I guess it doesn't
require any support code in the forms like the ListCount approach does, and is
not prone to code omission later.
I generally call a common function in the open event of every form,
something that then calls the ScaleForm from ADH so I'd put a common
function there to loop and do all combos. Only problem would be when
requerying later on.
That's exactly what I am doing, and that's exactly the problem. Someone has
to search the app for cases of combo box requerying and rowsource assignment,
and fix each one. Then, everyone has to remember from then on to do the same
for any new cases that are introduced.
At least I was able to automate the process of inserting a call to a procedure
that fills all combo boxes on a form into the Form_Load handler of any form
that doesn't already have the procedure call.
Steve Jorgensen wrote: On Tue, 15 Feb 2005 15:44:15 -0600, "Rick Brandt"
<ri*********@hotmail.com> wrote: You can also specify NO LOCK if using a pass-through for the
List/Combo and that will also prevent the problem.
Where do you specify NO LOCK? Specifying "No Locks" on the form
definitely does not help.
In a pass-through query to a SQL Server.
SELECT FieldName
FROM TableName (NOLOCK)
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
John Winterbottom wrote: "Rick Brandt" <ri*********@hotmail.com> wrote in message
news:37*************@individual.net... Steve Jorgensen wrote:
You can also specify NO LOCK if using a pass-through for the
List/Combo and
that will also prevent the problem.
I can see two potential problems with using a NO LOCK hint. First,
it's only supported by SQL Server. Second, the resultset becomes
read-only - so you can't re-use the same procedure for a
records-updating function later on.
Why would I care if the query used to populate a ListBox or CombBox was
editable?
Although it involves writing more code i think a better way might be
to use callback functions and a forward-only recordset to populate
lists and combo boxes.
I did this in a couple of cases where I was using a larger list to populate
the control than I am ordinarily comfortable with. If the list of rows is
small the entire set is likely to be pulled initially anyway. A Call-Back
on such a large set would perform terribly. Also the reason I don't like
the RowCount solution. It solves the problem by forcing all of the rows to
be pulled over the network. I see no reason to do that if I don't have to
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
On Wed, 16 Feb 2005 09:04:14 -0600, "Rick Brandt" <ri*********@hotmail.com>
wrote: John Winterbottom wrote: "Rick Brandt" <ri*********@hotmail.com> wrote in message
news:37*************@individual.net... Steve Jorgensen wrote:
You can also specify NO LOCK if using a pass-through for the
List/Combo and
that will also prevent the problem.
I can see two potential problems with using a NO LOCK hint. First,
it's only supported by SQL Server. Second, the resultset becomes
read-only - so you can't re-use the same procedure for a
records-updating function later on.
Why would I care if the query used to populate a ListBox or CombBox was
editable?
Although it involves writing more code i think a better way might be
to use callback functions and a forward-only recordset to populate
lists and combo boxes.
I did this in a couple of cases where I was using a larger list to populate
the control than I am ordinarily comfortable with. If the list of rows is
small the entire set is likely to be pulled initially anyway. A Call-Back
That doesn't match with my experience. One of the cases in which I
experienced the deadlock had to do with a 48-row table.
on such a large set would perform terribly. Also the reason I don't like
the RowCount solution. It solves the problem by forcing all of the rows to
be pulled over the network. I see no reason to do that if I don't have to
Steve Jorgensen wrote: On Wed, 16 Feb 2005 08:17:48 +0000, Trevor Best <no****@besty.org.uk> wrote:
...
Hmm - I had thought of that, and dismissed it as overengineering since getting
the ListCount works. Thinking more about it, though, I guess it doesn't
require any support code in the forms like the ListCount approach does, and is
not prone to code omission later.
I generally call a common function in the open event of every form,
something that then calls the ScaleForm from ADH so I'd put a common
function there to loop and do all combos. Only problem would be when
requerying later on.
That's exactly what I am doing, and that's exactly the problem. Someone has
to search the app for cases of combo box requerying and rowsource assignment,
and fix each one. Then, everyone has to remember from then on to do the same
for any new cases that are introduced.
At least I was able to automate the process of inserting a call to a procedure
that fills all combo boxes on a form into the Form_Load handler of any form
that doesn't already have the procedure call.
But you still have to remember to bind new combo boxes to a procedure
rather than a rowsource :-)
--
This sig left intentionally blank
"Rick Brandt" <ri*********@hotmail.com> wrote in
news:37*************@individual.net: I did this in a couple of cases where I was using a larger list to
populate the control than I am ordinarily comfortable with. If
the list of rows is small the entire set is likely to be pulled
initially anyway. A Call-Back on such a large set would perform
terribly. Also the reason I don't like the RowCount solution. It
solves the problem by forcing all of the rows to be pulled over
the network. I see no reason to do that if I don't have to
Um, any combo/list box that is slow in getting the RowCount has too
many rows being retrieved in the first place.
An I would never use a callback function for any rowsource that can
be retrieved via SQL -- it just performs way too slowly.
Perhaps one approach to the problem of assigning rowsources after
the OnLoad event is to pass all such calls through a generic
function that accepts the Rowsource as a string and the control as a
control variable, assigns the rowsource and then retrieves the
Row/Listcount. This way, you could have whatever custom code for
calculating the Rowsource (I most often put this in the form's
module, because it's almost always specific to the particular form),
and instead of assigning the result of your function that returns
the Rowsource, pass it through the other function. Instead of:
Me!cmbMyComboBox.Rowsource = ReturnRowsource()
you'd do:
Call AssignRowsource(ReturnRowsource(), Me!cmbMyComboBox)
Yes, for now, you have to go back and fix all your old code. But
once you've started doing it, it really takes very little more code
than the direct assignment.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
On Thu, 17 Feb 2005 03:21:22 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote: "Rick Brandt" <ri*********@hotmail.com> wrote in
news:37*************@individual.net:
I did this in a couple of cases where I was using a larger list to
populate the control than I am ordinarily comfortable with. If
the list of rows is small the entire set is likely to be pulled
initially anyway. A Call-Back on such a large set would perform
terribly. Also the reason I don't like the RowCount solution. It
solves the problem by forcing all of the rows to be pulled over
the network. I see no reason to do that if I don't have to
Um, any combo/list box that is slow in getting the RowCount has too
many rows being retrieved in the first place.
True, but many of our clients have them anyway, and they aren't paying us for
enough time to fix all of their design snafus.
And I would never use a callback function for any rowsource that can
be retrieved via SQL -- it just performs way too slowly.
In this case, though, doing so would eliminate the need for a large number of
code changes in each form, and the requirement that every developer know and
remember to use the new paradigm for all new cases.
Perhaps one approach to the problem of assigning rowsources after
the OnLoad event is to pass all such calls through a generic
function that accepts the Rowsource as a string and the control as a
control variable, assigns the rowsource and then retrieves the
Row/Listcount. This way, you could have whatever custom code for
calculating the Rowsource (I most often put this in the form's
module, because it's almost always specific to the particular form),
and instead of assigning the result of your function that returns
the Rowsource, pass it through the other function. Instead of:
Me!cmbMyComboBox.Rowsource = ReturnRowsource()
you'd do:
Call AssignRowsource(ReturnRowsource(), Me!cmbMyComboBox)
Yes, for now, you have to go back and fix all your old code. But
once you've started doing it, it really takes very little more code
than the direct assignment.
That's what I've ended up doing. Since this app is rife with large-result
combo boxes, I figure the speed cost of the callback function is too high.
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
An I would never use a callback function for any rowsource that can
be retrieved via SQL -- it just performs way too slowly.
The speed penalty is insignificant, and is more than compensated for by the
added control you get over when and how recordsets are opened and closed; so
that you don't leave a bunch of locks hanging around on the server.
Pass-through queries just don't scale very well.
Also, having programmatic access to the rows and columns in a list can be
handy for other stuff like formatting a date column.
"John Winterbottom" <as******@hotmail.com> wrote in
news:37*************@individual.net: "David W. Fenton" <dX********@bway.net.invalid> wrote in message
An I would never use a callback function for any rowsource that
can be retrieved via SQL -- it just performs way too slowly.
The speed penalty is insignificant, and is more than compensated
for by the added control you get over when and how recordsets are
opened and closed; so that you don't leave a bunch of locks
hanging around on the server. Pass-through queries just don't
scale very well.
Also, having programmatic access to the rows and columns in a list
can be handy for other stuff like formatting a date column.
???
In the QBE grid, you can format the date of SQL, too, simply by
setting it in the column's properties sheet (no need to call the
Format() function).
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: DotNetJunkies User |
last post by:
I am writing a distributed transaction code. My current scenario include a client database(Suppose client- having 4 main database) which can be installed anywhere which would connect to a public...
|
by: wrytat |
last post by:
Hi! I'm very new to ASP.NET and really need some good advice from experts
here.
I'm creating a web application for my company now. This application has 2
parts. 1 part for the customers to...
|
by: Rosco |
last post by:
Does anyone have a good URL or info whre Oracle and Access are
compared to one another in performance, security, cost etc. Before
you jump on me I know Oracle is a Cadillac compared to Access the...
|
by: Lester Moreno |
last post by:
Hello all,
Up to now C# have been an great experience but I found myself in a end of
the road problem.
Let say that you have two windows program running on a local network area.
User1 and...
|
by: Marc Gravell |
last post by:
Kind of an open question on best-practice for smart-client design. I'd
really appreciate anyones views (preferably with reasoning, but I'll take
what I get...). Or if anybody has any useful links...
|
by: Jeff |
last post by:
I know this is a difficult one to answer, but I am interested in opinions on
what hardware upgrades would be recommended with the following.
Access 2000 running in a split config, but all in...
|
by: B1ackwater |
last post by:
We've fooled around with Access a bit, but only using the
single-user store-bought version. It seems to be a good
database - versatile and infinitely programmable - and
can apparently be used as a...
|
by: Jordan S. |
last post by:
SQL Server will be used as the back-end database to a non trivial client
application.
In question is the choice of client application:
I need to be able to speak intelligently about when one...
|
by: shamirza |
last post by:
· What is view state and use of it?
The current property settings of an ASP.NET page and those of any
ASP.NET server controls contained within the page. ASP.NET can detect
when a form is requested...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
