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