Access 2003 to SQL Server 2000 over a VPN

[James Franklin]

Hi Rob, Sylvain,

I was planning to close each connection immediately it is no longer
required. For example, when I open a form, I would open a connection,
populate the form and close the connection again. When saving an updated
record, open a connection, save the record, close connection.

I know this results in slightly slower performance, but would this not be
more efficient, bandwidth-wise, than opening a single connection and
maintaining it while the application is running? I realise there is overhead
in negotiating the connection, but at least it is only for a fraction of a
second. Is there a significant overhead in maintaining a connection even
when nothing is happening?

Once again, I really appreciate the advice you guys are giving me,
Jim

 

[Robert Morley]

As far as I know (and I don't claim to be an expert here), the only
significant concern about leaving a connection open is the amount of memory
that it will take up. Small numbers of connections are nothing to worry
about, but if it's several thousand, it becomes more significant. As
always, it's a trade-off, though; is it worse to be maintaining the
connection, or is it worse to have the added load of re-negotiating the
connection all the time? As a general rule, I tend to think that
maintaining the connection is the better way to go, unless the app is likely
to be open 24/7. After all, the entire time your ADP is open, Access
maintains AT LEAST one connection, by default, sometimes more.



Rob

 

[Sylvain Lafontaine]

ADO connections are usually pooled, so closing or not a connection should
have no effect on performance on a well configured server. However, by
closing each connection as soon as possible, you will keep the size of the
pool at a minimum.

With ADP, there is a supplemental problem in the sense that ADP opens three
connections and sometimes and fourth and that the life length of these
connections opened by ADP is not documented.

 

[Robert Morley]

Good point, Sylvain...I was forgetting about ADO's connection pooling.


Rob

 

[Neil]

I don't know about performance with ADPs; but I have an MDB (with an MDB
back end) that's in A2000 format, and has been running great in A00 and
A02/03. Running it in A07, everything seems slower. Droping down a combo box
with a couple hundred items takes a few seconds (instead of instantaneous,
previously). Clicking on various tabs in a tab control results in a delay of
a second or two per click (again, instead of instantaneous). So, A07, in
general, seems to have some problems with performance, whether ADP or MDB.

Neil

 

[Neil]

I have a setup similar to this. The client runs a VP from Philly to NYC. The
ISP rerouted the line to go through Pittsburgh (since it's in the same state
as Philly, without considering that they were adding hundreds of miles to
the line). We saw a significant drop in performance just from that change.