Minimizing the number of connections that are made from each client

[Paul James]

I'm going over a MS Knowledge Base article about improving performance in a
multiuser environment, and one of the recommendations is entitled:

Minimize the number of connections that are made from each client

The information goes on to describe why this can be helpful, and also says
"To optimize performance and network I/O and to reduce the multi-user stress
on the back-end database, design the client application to use a single
connection to the Jet database."

However, they don't say anything about how this is accomplished. I tried to
find something about this in Access Help, but I only found discussions about
connections with respect to Data Access Pages.

Can anyone tell me how to minimize the number of connections that are made
from each client, or direct me to an information source that would explain
how this is done?

Thanks in advance,

Paul

 

[Rick Lederman]

Paul,

I use VB6 and VB.Net as a front end and recently switched from DAO (every
recordset open uses a separate connection) to ADO to talk to the database
which you can have multiple recordsets all through a single connection. If
you are doing it with Access as a front end this probably will not help

Rick Lederman

 

[Paul James]

Thanks, Rick. Yes, I'm using Access.

Does anyone have any suggestions on how to minimize the number of
connections when you're using Access XP as the front end?

Paul

 

[Albert D. Kallal]

Thanks, Rick. Yes, I'm using Access.

Does anyone have any suggestions on how to minimize the number of
connections when you're using Access XP as the front end?

I don't have any suggestions...except to close your reocrdsets that you
opened via code. However, I should note that if you have 20, or 200
connections open, you will NOT see any difference in performance here.....

This is really a question of not being sloppy, and opening a zillion
connections when you don't need to, as each connection you open can take
some time.

However, the speed of the application does not change once you have those
connections opened. And, since you are using a ms-access front end, then
again you will likely not see any performance gains here. anyway.

In fact, you want to force at least ONE connection to be in effect at all
times when you are using a split database. So, if your application does NOT
force, or open a back end table at startup, then you should do so (this
applies to a split database that uses a file share).

 

[Tim Ferguson]

Does anyone have any suggestions on how to minimize the number of
connections when you're using Access XP as the front end?

.... umm ... how about just using the CurrentProject.Connection property for
everything? Is there any reason that a single adodb.connection object
cannot host more than one recordset?

Tim F

 

[david epsom dot com dot au]

I use VB6 and VB.Net as a front end and recently switched from DAO

(every recordset open uses a separate connection) to ADO to talk to
the database

DAO code doesn't normally have a separate connection for every
recordset, but if you've coded that way, then changing to ADO
does give you an opportunity to rewrite your code.

(david)