Issues using MS Access as a front-end to a MySQL database back-end?

Question

Two users wanted to share the same database, originally written in MS Access, without conflicting with one another over a single MDB file.

I moved the tables from a simple MS Access database to MySQL using its Migration Toolkit (which works well, by the way) and set up Access to link to those tables via ODBC.

So far, I've run into the following:

• You can't insert/update/delete rows in a table without a primary key (no surprise there).
• AutoNumber fields in MS Access must be the primary key or they'll just end up as integer columns in MySQL (natch, why wouldn't it be the PK?)
• The tables were migrated to MySQL's InnoDB table type, but the Access relationships didn't become MySQL foreign key constraints.

Once the database is in use, can I expect any other issues? Particularly when both users are working in the same table?

Answer 1

I know this topic is not too fresh, but just some additional explanations:

If you want to use MS Access effectively, especially with bigger, multiuser databases, please do the following:

• split your MDB into frontend application and backend (data only) files - you'll have two separate MDB files then.

• migrate all the tables with data and structure into external database. It can be: MySQL (works very well, no database size limitations, requires some more skills as it's not MS technology, but it is a good choice in many cases - moreover you can scale your backend with more RAM and additional CPUs, so everything depends on your needs and hardware capabilities); Oracle (if you have enough money or some kind of corporate license) or Oracle 10g XE (if this is not a problem, that the database size is limited up to 4 GB and it will always use 1 GB of RAM and 1 CPU), MS SQL Server 2008 (it's a great pair to have MS Access frontend and MS SQL Server backend in all the cases, but you have to pay for license! - advantages are: close integration, both technologies are form the same vendor; MS SQL Server is very easy to maintain an effective at the same time) or Express edition (same story like with Oracle XE - almost the same limitations).

• relink your MS Access frontend with backend database. If you selected MS SQL Server for the backend then it will be as easy as to use the wizard from MS Access. For MySQL - you have to use ODBC drivers (it's simple and works very good). For Oracle - please do not use the ODBC drivers from Microsoft. These from Oracle will do their work much better (you can compare the time needed to execute SQL query from MS Access to Oracle via Oracle ODBC and MS Oracle ODBC drivers). At this point you'll have solid database backend and fully functional MS Access frontend - MDB file.

• compile your MDB frontend to MDE - it will give you a lot of speed. Moreover, it's the only reasonable form of distributing MS Access application to your end users.

• for daily work - use MDE file with MS Access frontend. For futher MS Access frontend development use MDB file.

• don't use badly written ActiveX components to enhance MS Access frontend capabilities. Better write them yourself or buy the proper ones.

• don't believe into the myths that there are a lot of issues with MS Access - this is a great product which can help in may occassions. The problem is a lot of people assume it's a toy or that MS Access is generaly simple. Usually they generate a lot of errors and issues by themselves and their lack of knowledge and experience. To be successfull with MS Access it is important to understand this tool - this is the same rule, like with any other technology outhere.

I can tell you that I'm using quite advanced MS Access fronted to MySQL backend and I'm very satisfied (as a developer which is maintaining this application). My friends, the users are also satisfied as they feel very comfortable with the GUI (frontend), the speed (MySQL), they don't have any issues with records locking or database performance.

Moreover, it's important to read a lot about good practices and other people experiences. I would say that in many cases MS Access is a good solution. I know a lot of dedicated, custom made systems which started as an experiment in form of private MS Access database (MDB file) and then evolved to: splitted MS Access (MDE - frontend, MDB - backend) and finally to: MS Access frontend (MDE) and "serious" database backend (mainly MS SQL Server and MySQL). It's also important that you can always use your MS Access solution as a working prototype - you have ready to use backend in your database (MySQL - let's assume) and you can rewrite frontend to the technology of your choice (web solution? maybe desktop C# application - what you require!).

I hope I helped some of you considering the work with MS Access.

Regards, Wawrzyn http://dcserwis.pl