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

On Tue, Nov 18, 2008 at 1:10 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

> 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.

Another thing to remember is to never use null-able Boolean columns in
the postgresql tables.  Access thinks that Boolean nulls are false, so
it "optimistic locking" update statements will fail in the WHERE
clause of the SQL string passed to the server.


> 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.

You have two options, 1) build update-able or non-update-able views,
2) use pass-through queries.

Update-able views work will with access since preforms the update for
each individual tuple as part of optimistic locking.  So the rule
system limitations do not come into effect with Access.

> 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

Ya filters are poor performers on the client side.  A better solution
is to replace the data source SQL string with a filtering version.
These will execute very fast.

> Access also has no idea about server-side auto-generated primary keys.
> To get sensible behaviour you have to enable the Row Versioning feature

for me it works (kind of...).  Do you have "recognize unique indexs =
true" in your ODBC driver?  The Auto generated id become visible after
insertion.

> 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.

Ya after working with MS-Access for the last three years I am working
my way away from it.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug