[AccessD] Access and SQL Server

My connection leak is actually happening with a complex C# system I am designing.

My guess is that Access would use a single connection for all linked tables.  There is no reason to 
have more than one for all of the tables.

As I said, this is all new to me.  I will be doing some tests soon to look at the locking issues.  I 
am using the 2007 run time so I have whatever advantage might be available in terms of the latest 
stuff.  In fact a client has offered a copy of Access 2010 which I will probably take her up on.  At 
that point my runtimes will be using 2010 whatever that might mean.

John W. Colby
www.ColbyConsulting.com

On 3/2/2011 11:02 AM, Jim Lawrence wrote:
> Hi John:
>
> You are definitely going into new territory, to my experience on this one.
> Having a bound system, connecting over the internet, I would suspect would
> drain resources from your BE server, (20+ dedicated connections per remote
> station? One for every bound table?) And then there is that flaky internet
> in between.
>
> Me thinks there is a good reason for not exceeding the default 100
> connection...Have you calculated how much resources like bandwidth, CPU and
> Memory each connection requires?
>
> Fifteen years ago I tried to get a similar system working and failed but the
> internal design of Access may have improved dramatically since then. As I
> said before, hats off if you get it working.
>
> Jim
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, March 02, 2011 7:17 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access and SQL Server
>
> Because I was leaking connections, I looked it up.  SQL Server actually has
> a pool of 100
> connections, and that number can be changed (though I haven't figured out
> how).  Someone on the
> internet had set his to 1000 for some reason.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 3/2/2011 8:07 AM, Jim Lawrence wrote:
>> Hi Stuart:
>>
>> A number of years ago the philosophy was to have "tight-binding" (or you
>> could call it bound or forced-synchronization or a dedicated connection)
>> with the BE data server.
>>
>> Over a fixed LAN system, the older standard was possible but there were
>> problems. For example: Connect an Access FE to an MDB BE on a server and
> if
>> the server crashed or needed a remote reboot the MDB was very likely to
>> corrupted. (If some user was connected...Had this happen a number of years
>> ago while working on some government contracts and the client demanded
> some
>> thing better.)
>>
>> When BE SQL servers were first introduced, again the philosophy was to
> have
>> dedicated connections to the server. The result was server farms as
>> thousands of fixed connections were required, depending on the number of
>> users.
>>
>> Now a days, a single server, with only 20 connections can support 50K hits
>> (different users?) a day. When a request arrives, it is queued and the
>> connection is immediately terminated. When the server can handle the
>> request, it does so and then queues up to open up a new connection, to the
>> remote station. If the remote station responds immediately the response is
>> given and any data is transferred otherwise the connection is immediately
>> terminated and the response queue cycles and tries again later.
>>
>> In most cases, the user is blissfully unaware of what is going on in the
>> background as to them it appears that they have dedicated, synchronized,
>> bound connection.
>>
>> When working with a program such as Access, the programmer can force a
>> dedicated connection by holding the connection open but unfortunately,
> under
>> load conditions, the BE server will run out of resources, refuse to
> respond
>> and there goes that tight binding. Added to that is the type of
> connection.
>> A dedicated LAN connection is very different from an internet connection
> as
>> the internet by its nature is flaky and unstable and again there goes that
>> tight binding.
>>
>> So there is my interpretation and a lot more detailed than I wanted to get
>> in to.
>>
>> Jim
>>
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan
>> Sent: Tuesday, March 01, 2011 3:55 PM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Access and SQL Server
>>
>> See inline.
>> On 1 Mar 2011 at 15:26, Jim Lawrence wrote:
>>
>>> I would suggest that most MS SQL applications are web based and
>>> therefore are using asynchronous type connections... they connect,
>>> call to a SP and wait for the remote server to respond. When the data
>>> is ready for receipt your Ajax connections notes data and the
>>> population of the recordsets begin, then connection is terminated.
>>>
>>
>> That is a perfect description of a "synchronous" connection.  Send a
> request
>> and wait for a
>> reply before doing anything else.
>>
>>> I would suspect that MS SQL runs similar to Oracle. Oracle just has
>>> more of its internal features exposed so I doubt whether there is any
>>> difference. When accessing data on an Oracle server the data request
>>> is queued, when the system has time it checks the request and then
>>> retrieves any data. It then calls the remote site indicating that the
>>> data ready, when the remote site says 'yes', the data is transferred.
>>>
>>> That does not describe a synchronous connection to me.
>>>
>>
>> No, that is asynchronous.  But that is only one of the ways Oracle works,
> it
>> also works
>> asynchronously:
>>
>>
>>>  From my reading of this link, synchronous connections are the default in
>> Oracle to. Note the
>> use of the word forced in the last sentence.:
>>
>>
> http://www.di.unipi.it/~ghelli/didattica/bdldoc/A97329_03/integrate.902/a952
>> 65/wfapi11.htm
>>
>> <quote>
>> A workflow process can be either synchronous or asynchronous. A
> synchronous
>> process is a
>> process that can be executed without interruption from start to finish.
> The
>> Workflow Engine
>> executes a process synchronously when the process includes activities that
>> can be
>> completed immediately, such as function activities that are not deferred
> to
>> the background
>> engine. The Workflow Engine does not return control to the calling
>> application that initiated
>> the workflow until it completes the process. With a synchronous process,
> you
>> can
>> immediately check for process results that were written to item attributes
>> or directly to the
>> database. However, the user must wait for the process to complete.
>> ...
>> An asynchronous process is a process that the Workflow Engine cannot
>> complete
>> immediately because it contains activities that interrupt the flow.
> Examples
>> of
>> activities that force an asynchronous process include deferred activities,
>> notifications
>> with responses, blocking activities, and wait activities.
>> </quote>
>>