0

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.

Improve this question
1
  • Are these really Access applications (with Access as the front end) or Jet applications (written in something else that use an MDB file as the data store)? Commented Nov 17, 2009 at 15:57

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

are you saying that you want to push as much work as possible to the database layer for best performance?
@JohnB: Especially queries that combine multiple tables should be moved to the server as a view. Access will load all records from all these tables before it can join them 'client side', while your database server will have no problem with joins on large tables.
oh so you are talking about Access(client)/MySQL(server) senario. I mostly develop C# apps, and I was thinking you meant always have the database server do all of the data manipulation (i.e. get the record set back exactly how you need it) instead of sometimes (case-by-case basis) doing some manipulation at the application layer. I tend to think of a database call as expensive. Btw, I developed an Access -> ODBC -> MySQL app and that stack worked great!
0

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.

Improve this answer

2 Comments

Rewriting it in .NET could take months, so you'd likely get to your data a lot quicker leaving the app in Access. For that matter, performance in Access with ODBC data sources is not in and of itself a performance bottleneck, except if you're doing it wrong.
Please explain how performance would be slower in Access than rewriting the app.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.