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

Craig Ringer wrote:
> If I'm wrong about any of this (which is not unlikely, really) then if
> anyone else is "lucky" enough to be using Access with PostgreSQL and
> knows of a better solution or workaround, please feel free to correct me.
>
We have been working with the combination of a PostgreSQL backend and an
Access frontend for several years. Since we still use Access 2003 I
cannot tell if things have become worse with Access 2007, but my
experiences with this combination are not too bad. I would agree that
there is some tricky stuff about it, and one should always consider
another solution (e.g. a web-based frontend), but I would not consider
the Access solution a "no-go".
> 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.
>
That's not exactly what I observed. Access actually knows about primary
keys (at least sometimes *g*). When you link the table it tries to find
the primary key fields and somehow stores this information. If no
primary key exists you will be asked to select one or more fields that
uniquely identify a record.
When you then open a linked table without any filter, Access fetches all
records from the primary key column(s). Of course this can already be a
bad idea, but it's at least better than fetching all the data. The next
step is to fetch a couple of records (50 when I just tried it)
identified by their primary keys (some nasty "SELECT .... <lengthy field
list> FROM <table> WHERE pk = ... OR pk = ... OR pk = ..." statements).
When you skip to another part of the table, the next block of records is
fetched.
> As far as I can tell there is no way to get it to execute even simple
> filters (think "WHERE id = 99") server-side while still using Access's
> built-in support for linked tables etc. If you want to do joins, filters,
>
I tried to apply an Access filter (not using a query) and the result was
again fetched in two steps: First the matching primary keys were
selected using a "where" clause ("SELECT <pk> FROM <table> WHERE
<condition>") and then the full data of the matching records was fetched
(this time with one query for each record).
When I create a query (not pass-through) that joins two tables I
actually get a join in the generated backend query. It's again the
two-step approach, fetching the primary keys first ("SELECT <pk1>, <pk2>
FROM <table1>, <table2> WHERE <condition> AND <join condition>") and
then the data. Interestingly, the data is fetched separately from both
tables after the matching records have been identified.

I would never praise Access the ideal frontend for PostgreSQL, but it
may come out the best solution when you need a quick and simple frontend
and bandwidth is not too restricted. Of course there are some problems
that must be considered:

    * Auto-generated keys are a problem.
    * Nullable booleans are not supported. (But you can use a smallint
      instead with only 0 and -1 allowed which works fine.)
    * Timestamps must always be clipped to precision 0.
    * Dates before 01.01.0200 are not supported.
    * ...

Regards,
    Christian

P.S.: I used Access 2003, a PostgreSQL 8.2 backend and the latest 8.2
version of the PostgreSQL ODBC driver.

--
Deriva GmbH                         Tel.: +49 551 489500-42
Financial IT and Consulting         Fax:  +49 551 489500-91
Hans-Böckler-Straße 2                  http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer