Multiple connections from Access Project to SQL Server

[Dan Ryan]

When I open the first form in my Access 2000 .adp file,
it opens two connections on my SQL Server, and keeps both
of them open for the life of the project instance. This
has licensing and performance implications. Has anybody
experienced this before, and if so, is there anything I
can do to stop it? I only want/need one connection.

 

[Mary Chipman]

It has no licensing implications (licensing is either per processor or
per CAL), and the performance implications are good. That's just SQL
Server doing its connection pooling thing to get data to your UI the
fastest way possible so your users aren't staring at the little
hourglass.The pool frequently gets populated with a couple of
connections before they are actually needed. If you're worried about
performance, write efficient queries and only fetch needed data.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Dan]

Thanks for the response. We distribute MSDE with our
software, and I am concerned that the 5
connection "ceiling" would be affected by this behavior.
If it doesn't affect MSDE, then I personally don't care.
One of my customers has a DBA that is making an issue of
my application opening too many connections on her
server. I have considered throwing the monkey on her back
to prove that this is a real-world problem, but figured
I'd learn more about before I took that route. Any
ammunition you could give me would be greatly appreciated.
- Dan

 

[Vadim Rapp]

D> One of my customers has a
D> DBA that is making an issue of my
D> application
D> opening too many connections on her server. I have
D> considered throwing the monkey on her back to prove
D> that this is a
D> real-world problem, but figured I'd
D> learn more about before I took
D> that route. Any
D> ammunition you could give me would be greatly


for example, http://support.microsoft.com/?kbid=248966 says: "An Access
project actually has two connections to your database".


Vadim

 

[Brian M. Sockey]

Unfortunately there isn't a way to change this behavior. There's actually
three primary connections that an ADP uses and there's no way to force it
into just one. The following article I wrote regarding application roles
discusses this:

http://support.microsoft.com/?kbid=308312

Since MSDE performance is only affected when there are more than 5 "active"
connections running against SQL Server, it doesn't matter how many
connections that Access has open, only how many happen to be actively
querying at one time. However, as Mary pointed out, this generally
improves performance overall because Access will finish using its
connections sooner, even though it may use more than one at a time.

Brian M. Sockey
www.farsightsolutions.com
www.televantagenorthwest.com

 

[Brian M. Sockey]

Actually, you'll still see this behavior even if you turn connection pooling
off by editing the registry. The Access developers over at Microsoft
designed it this way at the client end. I just wish they'd given us
programmatic access to all these connections and not just one. Really
mucks up the use of application roles.

Brian M. Sockey
www.farsightsolutions.com
www.televantagenorthwest.com

 

[Mary Chipman]

There is no such thing as a 5 connection ceiling. What there is is a
workload governor which acts as a performance throttle. This kicks in
at 5 concurrent *batches*, not connections. If you write efficient
queries and fetch only needed data you're not even going to see this.
SQL Server/MSDE has sub-second response time on queries unless you're
fetching mountains of data, so in an efficiently crafted app you're
not going to hit the performance throttle unless you have many many
times 5 concurrent connections. And even if you do, things just slow
down--the app doesn't croak.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446