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@archonet.com>
To: "Craig Ringer" <craig@postnewspapers.com.au>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Tuesday, November 18, 2008 3:46 AM
Subject: Re: [GENERAL] 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general