linked tables to odbc/postgres... lots of connections & disconnections

[TD]

Hello All-

We are in the process of converting the back end of our large MS
Access application to PostgreSQL. My question today concerns linked
tables and how their connections are managed by Access. It appears that
Access does a *lot* of connecting and disconnecting to these linked
tables. This is causing me a major headache, as I'd really like to
store some session-based information in the back end that can be
referenced by the linked tables.

Does anybody have experience with this? I'd appreciate some thoughts.
Is there any way to make Access make just one connection and hold onto
it? My hope is to have views in postgres reference session information,
but with the sessions continually getting dropped and remade, there is
no hope.

Thanks,
-Tom

[david epsom dot com dot au]

Sorry. Just another 'optimisation' from the Access/Jet
team.

At the risk of being repetitive, what /really/
irritates me about these connections is that there
is no way to run a transaction: the transaction starts
multiple connections, and they block each other.

Believe me, if there was a way to make Access use
just one connection and hold onto it, I would.

You will also probably find that there is no way
to embed meaningful information in the connection
string. Anything with any meaning is overwritten
by Access: stuff without meaning is ignored by
your server. However, that is only known for MS
SQL Server - there may be some way to pass arbitrary
information through the PostgreSQL driver.
(david)

"TD" <to*******@gmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...

Hello All-

We are in the process of converting the back end of our large MS
Access application to PostgreSQL. My question today concerns linked
tables and how their connections are managed by Access. It appears that
Access does a *lot* of connecting and disconnecting to these linked
tables. This is causing me a major headache, as I'd really like to
store some session-based information in the back end that can be
referenced by the linked tables.

Does anybody have experience with this? I'd appreciate some thoughts.
Is there any way to make Access make just one connection and hold onto
it? My hope is to have views in postgres reference session information,
but with the sessions continually getting dropped and remade, there is
no hope.

Thanks,
-Tom

[Larry Linson]

"david epsom dot com dot au" wrote

Sorry. Just another 'optimisation' from
the Access/Jet team.

At the risk of being repetitive, what /really/
irritates me about these connections is that there
is no way to run a transaction: the transaction starts
multiple connections, and they block each other.

Believe me, if there was a way to make Access use
just one connection and hold onto it, I would.

You will also probably find that there is no way
to embed meaningful information in the connection
string. Anything with any meaning is overwritten
by Access: stuff without meaning is ignored by
your server. However, that is only known for MS
SQL Server - there may be some way to pass arbitrary
information through the PostgreSQL driver.

Jet does not dink with the contents of Passthrough Queries. I assume that
the PostgreSQL ODBC driver allows Passthrough Queries.

If you are allowed to create stored procedures in your environment, perhaps
you could do your transaction in a stored procedure. (I am aware that the
Database Administrators in many business environments do not allow "mere
front-end programmers" to dink around with their server databases by doing
such things as creating stored procedures.)

Perhaps what you want to do can be done with an ADP. If so, it is one of the
few reasons that using ADP would be good, IMNSHOBOPWOIVSDWMAANTMMPWOIDNV (In
my not-so-humble opinion, but other people whose opinions I value sometimes
disagree with me about ADP, not to mention many people whose opinions I do
not value).

Larry Linson
Microsoft Access MVP