 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2005Important Access Client/Server tip
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. > 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 [quoted text clipped - 24 lines] > 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.  SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com >> 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 [quoted text clipped - 27 lines] >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. >> 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) SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com > 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. >> You can also specify NO LOCK if using a pass-through for the List/Combo >> and [quoted text clipped - 3 lines] >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. >>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. SignatureThis sig left intentionally blank ... >> 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 [quoted text clipped - 5 lines] >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. > ... > [quoted text clipped - 16 lines] > 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 :-) SignatureThis sig left intentionally blank >> You can also specify NO LOCK if using a pass-through for the >> List/Combo and [quoted text clipped - 4 lines] > 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 SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com >>> You can also specify NO LOCK if using a pass-through for the >>> List/Combo and [quoted text clipped - 15 lines] >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 > 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 [quoted text clipped - 3 lines] > 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. SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc >> 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 [quoted text clipped - 6 lines] >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 [quoted text clipped - 15 lines] >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" <dXXXfenton@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. > "David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message >> [quoted text clipped - 9 lines] > 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). SignatureDavid W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.