473,395 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 2091
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
30
by: btober | last post by:
Whenever I create a temporary table, with something like CREATE TEMPORARY TABLE temptable1 AS SELECT * FROM paid.ad_hoc_query; New schemas appear, with names like "pg_temp_1". I guess the...
2
by: Jeff Pritchard | last post by:
Some time ago I am sure I came across something that said this was possible, though it doesn't seem to work. A client wants to replace an Access back-end with SQL Server tables. We have tried...
3
by: Enzo's Dad | last post by:
I have an Access frontend for an SQL backend. I'm simply using ODBC to connect. On my copy of this Access database, I have four linked tables and all works well. If I copy the Access MDB to...
4
by: Neil Ginsberg | last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked tables do not have the password stored in them, so the first time the user accesses them, they need to enter the SQL...
7
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
2
by: Jill Elaine | last post by:
I am building an Access 2002 frontend with linked tables to an encrypted Paradox 7 database. When I first create these linked tables, I'm asked for the password to the encrypted Paradox database,...
5
by: Neil | last post by:
I'm running Access 2000 with a SQL 7 back end, using ODBC linked tables in an MDB file. The db is used by about 30 users on a LAN, and an additional 10 or so on a WAN. Recently, one of the WAN...
8
by: brucedodds | last post by:
I've inherited an A2003 application with linked SQL Server 2000 tables in the back end, using the Microsoft SQL Server ODBC driver. We've set up a test SQL Server database. I'd like to automate...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.