ADP Project uses 3 connections on SQL server ?

  • Thread starter Thread starter John Skix
  • Start date Start date
J

John Skix

I Recently migrated an Access 2000 database to SQL server using an Access
adp project as front end.
When a user logs on, I can see 3 connections per user open in Enterprise
Manager for the application even if no data has been retrieved from the
server at all.

Being my first attempt in migrating to SQL server, I was wondering if this
is normal behavior.

Any help is appreciated.

Regards,
John
 
Thanks for the reply Sylvain.

Do you know if
1) The number of users that can logon to the database are more limited (I
use SQL server Enterprise BTW) because of the extra connections ?
2) adp frontends work less efficient then (regular) apps that use only one
connection, or is this just another way of working ?

regards
John
 
Personally, I never hit any limitation in the number of connections, so I
never searched this topic. The exact relationship between ADP and
connections: how long they are connected, are they closed regularly, are
they reopened automatically, how well is the connection pool used, etc.;
have never been clearly explicited by any MS documentation.

But excerpt for Application Role, I don't remember anyone having problem in
this regard. The only one that I can think of is for some bugs (or *design*
problems) in the connection pools when many different applications use it
with different kinds of parameter connections; for exemple when you are
connecting to a public SQL-Server hosted by a public web hosting service.
The usual solution is usually to isolate yourself from the other connections
by adding some strange parameter in the connection property or simply by
deactivating the connection pool in your case.

Specific to your problem, it is my opinion that inactive connection will be
closed and recycled by the SQL-Server connection pool and that you will have
a problem only if the number of active connections - ie connections wanting
to do some usefull work or having an opened transaction at the same time -
reach the maximum number of connection available on the SQL-Server. I
don't know what is this maximum (100?) but all you will have to do is to
augment this number if necessary.

Also, *regular* applications may often use more connections than you think.
Also, I don't see why you thinking that using more connections will result
in a less efficient work. The real problem is usually the work done by the
SQL-Server, not the number of open connections.
 
Back
Top