ODBC, connection pooling

[Zlatko Matiæ]

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

[Steve]

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.

[David W. Fenton]

"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in
news:do**********@ss405.t-com.hr:

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.

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

[Zlatko Matic]

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

"David W. Fenton" <XX*******@dfenton.com.invalid> je napisao u poruci
interesnoj grupi:Xn**********************************@127.0.0 .1...

"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in
news:do**********@ss405.t-com.hr:

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.

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

[Steve]

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.

[Zlatko Matic]

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" <th*********@gmail.com> je napisao u poruci interesnoj
grupi:11**********************@g49g2000cwa.googleg roups.com...

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.