Access, ODBC, and connections

[listrecv]

Is there anyway to tell Access to use ODBC Connection Pooling, for
tables linked (from SQL Server), via ODBC?

If not, is there at least a way to get Access to keep the connection
open? Currently, Access disconnects and reconnects for every form
control, for every update!

Any help or ideas greatly appreciated.

 

[Albert D.Kallal]

Are you using windows authentication here...or actual setting a user +
password for every connection string?

Using windows authentication here allows sql to pool much better.

Access disconnects and reconnects for every form
control, for every update!

The above is not correct. For each listbox...or combo box..and a sub-form,
sure...this might be the case (depending on how you setup your connection to
sql server).

However, for each normal control on a form...a connection does not occur,
and if it is..then something is wrong..very wrong (either with you
setup...or your design). If you have a bunch of controls that using
dlookup()..then once again, I can see some serous problems....but then
again, for the most part we avoid those silly domain commands...and
especially when using sql server (eg: most dlookups on a form can be
replaced by building a view on the sql side that joins in the related table
lookup values - you then link the form to that view).

 

[Albert D.Kallal]

I also assume that you checked the settings for the odbc driver..and have
enabled connection pooling?

(bring up the control panel...odbc32...and check the settings for sql
server....There is a tab..and you can enable the pooling...

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[listrecv]

Albert,

Thanks for the info. I'm going to look into and get back!

 

[listrecv]

Albert,

Thanks for the info. Here's what I found:

1. I switched it to Windows authentication.

2. I was able to confirm what you stated, that new connections are not
done for all controls, only certain ones (like list boxes).

3. Yes, ODBC Connection Pooling was enabled, although I don't think
that Jet uses it (it needs to be requested by the ODBC Client).

Nonetheless, Access still hangs up and reconnects, for those particular
controls.

I came across this white paper (formerly on MSDN) which may shed some
light:
http://web.archive.org/web/20041028125124/http://www.microsoft.com/accessdev/articles/Jet3ODBC.htm

"An active statement is a query whose results have not been completely
fetched from the server. Some servers/drivers do not allow any other
statements to be executed on a single connection if there is an active
statement on that connection. In this case, Jet may use multiple
connections (for example, when updating a record before the entire
dynaset is fetched)... Jet maintains connections even when they are not
explicitly in use, to avoid constantly disconnecting and reconnecting.
This is invisible to the user. The number of idle connections
maintained depends on the value of SQL_ACTIVE_STATEMENTS: If 1, then
two idle connections are maintained. If greater than 1, then one idle
connection is maintained."

So, it sounds like Jet wants to populate those controls using a
separate connection than the main connection that the form is using,
and hence needs to connect again. But it won't leave more than two
idle connections open, and so it needs to reconnect each time.
Unfortunately, using Windows authentication didn't solve this.

So, all this being said, is there anyway I can cause Jet to keep more
of those connections open (even if idle), or, alternatively, to reuse
the same connection as opposed to starting new ones?

 

[listrecv]

BTW, I found the whitepaper on MSDN, at a different URL:
http://msdn.microsoft.com/library/s...ODBCConnectivity.asp?frame=true&hidetoc=false
..

Any ideas on how to get Access to keep more connections, or reuse
existing ones, instead of spawning new ones (veerryy slllooooww over a
WAN), in this type of a situation?

 

[david epsom dot com dot au]

That looks familiar. Notice that it says the idle connections
should persist for 10 minutes. Is that what you are seeing?

(david)

 

[listrecv]

David,

No, I haven't seen that.

My understanding of the whitepaper is that idle connections will be
dropped either when idle for 10 minutes OR instantly when there are
more than 2.

 

[david epsom dot com dot au]

David,

No, I haven't seen that.

My understanding of the whitepaper is that idle connections will be
dropped either when idle for 10 minutes OR instantly when there are
more than 2.

Yes, I guess I understand that now.

What version of Access are you using (I just want to confirm that you aren't
use a really old copy of Access 2000).

I guess I don't see that kind of behaviour except when using transactions
(which are now broken on ODBC), because my code manages to fit into the two
connections. Two things: cached data where possible, and the forms are
bound to a local temp table (so the combo boxes don't load until after the
main record is already finished loading).

(david)