Right, passthrough queries is the answer. I once had to build an Access
front end to a very large Oracle database for a client. As you discovered,
directAccess queries can be extermely slow and large. With a passthrough,
all the real work is done on the host database and just the result you want
is returned.
Bayless
----- Original Message -----
From: "Richard Huxton" <dev@xxxxxxxxxxxx>
To: "Craig Ringer" <craig@xxxxxxxxxxxxxxxxxxxxx>
Cc: "PostgreSQL" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Tuesday, November 18, 2008 3:46 AM
Subject: Re: MS Access and PostgreSQL - a warning to people
thinking about it
Craig Ringer wrote:
Hi all
I've been forced into a project that uses MS Access with PostgreSQL.
[snip]
The big issue is with Access's linked table support via ODBC (at least
as of Access 2007). Unlike tools like Hibernate, which are capable of
executing filters, queries across multiple tables, etc server-side,
Access will ALWAYS fetch the full contents of the linked table then do
its filters and joins client-side.
Yep - you want "pass through" queries if you want to execute them
server-side. You can "save" the query definition and use it as a view
within Access then though.
What I've had to do where I need proper parameterised query support is
build it myself from regular expressions and string-replacements. Ick.
This might not matter too much if your tables are small, your database
load is light, and you're on a fast link. If your tables are large and
your users want to work over a GSM/HSDPA mobile phone link, on the other
hand...
Access can, of course, transparently execute queries server-side if used
with MS SQL server, as it doesn't use its ODBC linked table support for
this but rather different and more capable features targeted
specifically at MS SQL Server.
I'm guessing they both share some library code for query parsing etc.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
