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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
