Access against MySQL or Postgres?

Question

I have a (potential) client who has had several a several Access applications previously developed which they want to upgrade to use a database back end. Unfortunately because of costs they don't want to use SQL Server, and SQL Express is likely inappropriate due to licensing restrictions.

They have mentioned using MySQL and an option - which I am sceptical of, and I'm considering recommending Postgres although my direct experience of that combination is limited.

Are either of these two databases feasible options? Which would be preferred and is there any other alternatives that should be considered?

ADDED: They are looking to replace the database but keep the Access front end. Apparently these would seem to be fairly simple applications but used across a large number of users (80+) hence the cost implications of SQL Server and unsuitability of SQL Express.

Answer 1

If you don't want to rewrite all of your application logic to you can link a mysql/mssql/postgres table in access using ODBC (Right click > Link table).

That said it will be slower than rewriting the app in say .net, and you won't be able to take advantage of some of the features that the databases support natively. Also db incompatibilities will cause some strange bugs.

Answer 2

We have very good experiences with Access -> ODBC -> MySQL / Firebird. I assume PostGres should be as easy.

This will not solve all your performance issues (if you have any). The best thing to do to gain significant performance increase is to use views / stored procedure / pass through queries to bring as much load as possible to the (fast) database server.

Answer 3

Actually there was at least one newsgroup posting indicating that the poster had 75+ users on SQL Server Express 2005 edition. The key is that it's the maximum number of connections that are throttled and not the number of users that are connected. Furthermore those that are throttled just wait until the first five get done. So if your app doesn't waste a lot of time doing "batch" style processing then it could work just fine.

OTOH you would be pushing the limits so I can understand why you wouldn't want to try this and then discover it wasn't going to work.

OTTH (On the third hand) possibly multiple instances of SQL Server Express could be installed and each of the applicatins uses their own instance to keep the number of connections down. I'm just guessing that this could be done. I'd also understand why MS would not allow you to game the system in this fashion.