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.
Nov 13 '05
14 2193
Steve Jorgensen wrote: On Wed, 16 Feb 2005 08:17:48 +0000, Trevor Best <no****@besty.o rg.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*********@ho tmail.com> wrote in
news:37******** *****@individua l.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!cmbMyComboBo x.Rowsource = ReturnRowsource ()
you'd do:
Call AssignRowsource (ReturnRowsourc e(), Me!cmbMyComboBo x)
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********@bwa y.net.invalid> wrote: "Rick Brandt" <ri*********@ho tmail.com> wrote in news:37******* ******@individu al.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!cmbMyComboBo x.Rowsource = ReturnRowsource ()
you'd do:
Call AssignRowsource (ReturnRowsourc e(), Me!cmbMyComboBo x)
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********@bwa y.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******@hotma il.com> wrote in
news:37******** *****@individua l.net: "David W. Fenton" <dX********@bwa y.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 database placed at ISP datacentre(Suppose Server- having 2 database). this server database is added to client as linked server through vb.net interface. i have a transmission routine which transmitts the data from the client to Server and update client...
|
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 access. The 2nd part is for our staff to
access only. My director hopes to make the 2nd part to be something like an
intranet, such that only our company's computers (maybe only 1 or 2 in the
company) can login to this part of the application.
1. My...
|
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 Ford
Fairlane. I need this info to complete a school project. Thanks.
|
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 user2 have the same information in the dataset and user1 makes a
change, unless user2 refill all his tables in his dataset he can't see the
|
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 on the subject? (and yes,
I have already googled it at length, but still no strong decision)
=============
After a long stint of pure-desktop / pure-server applications, I'm currently
working on a number of smart-client projects in C# using...
| |
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 Terminal Server. Currently
15 users. This means that all files reside on the server and all processing
happens there. Back-end is currently Access, but it will be upsized to SQL
Server . So for this question assume SQL Server back-end.
So what...
|
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 front end to SQL server if
we ever needed to go that route.
But - is there a client/server version of Access ? Looking
on the CDW site there is a bewildering variety of packages
and licences and such, but we can't figure out just which
do...
|
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 client (MS Access vs
..NET Windows Forms) would be preferred over the other. While I have some
good arguments on both sides, I would appreciate your points of view on the
topic.
|
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 for the first time versus when the form is
posted (sent to the server), which allows you to program accordingly.
· What are user controls and custom controls?
Custom controls:
A control authored by a user or a third-party software vendor that...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |