Обсуждение: 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

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

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

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

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