access前端MySQL后端,使用MS Access作为MySQL数据库后端的前端的问题？

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?

解决方案

I had an application that worked likewise: an MS Access frontend to a MySQL backend. It was such a huge pain that I ended up writing a Win32 frontend instead. From the top of my head, I encountered the following problems:

Development of the ODBC link seems to have ceased long ago. There are various different versions floating around --- very confusing. The ODBC link doesn't support Unicode/UTF8, and I remember there were other issues with it as well (though some could be overcome by careful configuration).

You probably want to manually tweak your db schema to make it compatible with MS Access. I see you already found out about the needed surrogate keys (i.e., int primary keys) :-)

You should keep in mind that you may need to use pass-through queries to do more sophisticated SQL manipulations of the MySQL database.

Be careful with using lots of VBA, as that tends to corrupt your frontend file. Regularly compressing the database (using main menu, Tools | Database utilities | Compress and restore, or something like that --- I'm using the Dutch version) and making lots of backups is necessary.

Access tends to cause lots of network traffic. Like, really huge lots. I haven't been able to find a solution for that. Using a network monitor is recommended if you want to keep an eye on that!

Access insists on storing booleans as 0/-1. IMHO, 0/+1 makes more sense, and I believe it is the default way of doing things in MySQL as well. Not a huge problem, but if your checkboxes don't work, you should definitely check this.

One possible alternative would be to put the backend (with the data) on a shared drive. I remember this is well-documented, also in the help. You may want to have a look at some general advice on splitting into a frontend and a backend and code that automatically reconnects to the backend on startup; I can also send you some more sample code, or post it here.

Otherwise, you might also want to consider MS SQL. I don't have experience with that, but I presume it works together with MS Access much more nicely!