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

Hi all

I've been forced into a project that uses MS Access with PostgreSQL.
This message is intended as a bit of a warning to others who actually
have a choice about it, as there are some issues you may not be aware of
that might sway your decision to use Access in a new project instead of
building an app yourself in Java, Qt, or whatever.

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.


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.

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.

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, etc server-side you need to build your queries using Visual
Basic and populate your tables using recordsets from VB, then
(apparently) manually UPDATE the database with the changes, again from
VB. This basically reduces Access to a GUI forms designer, something I
can do with Eclipse, NetBeans, or Qt Designer anyway.

(... but the user insists on Access, and the user in this case gets to
make the decisions. Yay.)



Access also has no idea about server-side auto-generated primary keys.
To get sensible behaviour you have to enable the Row Versioning feature
in the PostgreSQL driver (to prevent the driver or Access from querying
for records by ALL their attributes instead of just the primary key).
You then, in the Form_BeforeInsert event for the form, have to use then
use a passthrough query in Access to invoke the nextval(...) function
and set the primary key field to the value obtained.


Calling stored procedures is also somewhat exciting, apparently. I'm
currently trying to track down an issue where Access is issuing a SELECT
twice in a row (according to the database log) when OpenRecordset is
invoked on a query. This is less than helpful when the stored procedure
is being invoked to perform complex changes to the data server-side.
This also means you need to maintain the DSN conncection string in your
VB code as well as maintain the linked table connections.

So ... if you're thinking about using Access with PostgreSQL in a new
project, as opposed to (say) user reporting in an existing project or
integrating data from multiple sources, you might want to do some
testing and build a trivial prototype before you go ahead and start the
real thing.

--
Craig Ringer