469,290 Members | 1,691 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

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

Jun 8 '06 #1
2 1987
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

Jun 9 '06 #2
"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
Jun 10 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jeff Pritchard | last post: by
3 posts views Thread by Enzo's Dad | last post: by
4 posts views Thread by Neil Ginsberg | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.