ODBC, connection pooling

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Zlatko Matiæ - 21 Dec 2005 20:35 GMT

Hello.

In ODBC Data Source Administrator, there is a tab "Connection Pooling" and
option "PerfMon" Enable/Disable.
Could someone explain the meaning of the option and how it reflects to
Access when having ODBC linked tables, JET queries on linked tables and
pass-through queries?

Thanks,

Zlatko

Reply to this Message

Steve - 22 Dec 2005 16:30 GMT

Whenever you go out to your linked tables a connection must be
established to get the data. Without connection pooling, a new
connection is used each time the program requires a connection (i.e.
when you run a query on a linked table, when you open a linked table
directly, when you use a Recordset to return data from a linked table).
If you use connection pooling, however, your program can establish a
connection and then use that one connection each time it needs to get
data from the linked table. This can increase performance because
you're using fewer connections to accomplish the same task.

Reply to this Message

David W. Fenton - 22 Dec 2005 21:04 GMT

> In ODBC Data Source Administrator, there is a tab "Connection
> Pooling" and option "PerfMon" Enable/Disable.
> Could someone explain the meaning of the option and how it
> reflects to Access when having ODBC linked tables, JET queries on
> linked tables and pass-through queries?

Access can't use ODBC to connect to Jet data, so, basically, the
question has zilch to do with Access.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

Zlatko Matic - 23 Dec 2005 07:56 GMT

I have linked tables (PostgreSQL), so the question is regarding such
combination.

"David W. Fenton" <XXXusenet@dfenton.com.invalid> je napisao u poruci
interesnoj grupi:Xns9734A3986F73Cf99a49ed1d0c49c5bbb2@127.0.0.1...

>> In ODBC Data Source Administrator, there is a tab "Connection
>> Pooling" and option "PerfMon" Enable/Disable.
[quoted text clipped - 4 lines]
> Access can't use ODBC to connect to Jet data, so, basically, the
> question has zilch to do with Access.

Reply to this Message

Steve - 23 Dec 2005 15:55 GMT

Zlatko sent this question to my email address. Zlatko, please post
questions and answers to the newsgroup so anyone else with the same
question or a better answer can find the information.

>From Zlatko:

Thanks for answer.
What happens when an Access aplication with linked tables are closed?
Does "connection pooling" connections close as well?

Greetings,

Zlatko

---------------------------

To the best of my knowledge (and with an obvious logical assumption),
all connections close when Access closes. Links to Access and dBASE IV
tables reopen automatically when Access is opened. A coworker is
working on an Access front-end with a PostgreSQL back-end right now,
and we've found that the connection to PostgreSQL does not
automatically reopen when Access is opened. Instead, the connections
are reestablished the first time someone tries to access the linked
data. I'm assuming this is because of ODBC. Note that we had to
authenticate for every table that was linked programmatically (i.e. 20
tables linked, 20 credential requests) before we tried connection
pooling in the connection string. Your user will still have to
authenticate after the Access application is opened when he (through
the program) attempts to access linked data.

We have not yet found a way to cause the ODBC connection to
automatically resume when the database is opened, so instead we have
placed code that accesses a small amount of linked data when the
application is opened. This causes the authentication process to
initiate, and the user only has to auth once for the duration of their
usage of the application.

Reply to this Message

Zlatko Matic - 26 Dec 2005 21:53 GMT

I have a "startup form" to enter username and password. After clicking the
confirmation button, a procedure starts that relink all linked tables, by
using programmatically created connection string. So, all tables get the
same connection string during the startup of the application, and use it
during the session. Authentication takes place only once, on the beginning
of a session.
I measured time of queries execution with and without PerfMon enabled, and
couldn't see any difference in performance. Maybe because both front-end and
back-end are on the same computer? Maybe the difference would be much bigger
in intranet enviroment?

Regards,

Zlatko

"Steve" <theonesteve@gmail.com> je napisao u poruci interesnoj
grupi:1135353335.722412.152900@g49g2000cwa.googlegroups.com...

> Zlatko sent this question to my email address. Zlatko, please post
> questions and answers to the newsgroup so anyone else with the same
[quoted text clipped - 31 lines]
> initiate, and the user only has to auth once for the duration of their
> usage of the application.

Reply to this Message