Link Tables and Connection Pooling

[aedwards via AccessMonster.com]

In an Access application, when you create ODBC links to SQL Server tables
(Tables->New->Link Table->ODBC) is there a way to make those ODBC connections
use connection pooling?

Things were grinding to a standstill, then I went into the SSMS Activity
Monitor and noticed tons of idle processes.

Can I just add something like "Connect Timeout=120; Min Pool Size=10" to the
connection string?

Thanks!

 

[Mary Chipman [MSFT]]

Here's the FAQ for ODBC connection pooling:

http://support.microsoft.com/kb/169470

Idle processes in and of themselves shouldn't cause things to grind to
a standstill. For that I think you may need to look elsewhere.

--Mary

 

[Sylvain Lafontaine]

Like Mary Chipman has said, I would be surprised if your problem comes from
something like too many connections opened. You can try to play with that
or with other things like changing the provider used for communicating with
the SQL-Server (for example, using the SQL-Server 2005 Native ODBC Provider
if you are working against a SQL-Server 2005 database) but I would be
surprised if you manage to achieve any real improvement with these types of
change.

In your case, the first thing to do would be to update the statistics using
the sp_updatestats procedure after that to clear the caches on the
SQL-Server:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
If this change nothing, then you will have to take a look at what are you
doing, both on your client side (are you trying to retrieve *all* records
each time? Etc.) and on the SQL-Server side (locking problem? Etc.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)