Re: MS Access and PostgreSQL - a warning to people thinking about it

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(at)archonet(dot)com>
To: "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au>
Cc: "PostgreSQL" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general