If you have table relationship involving referential integrity, exporting access tables directly via ODBC, they go away and if you have junk characters in table or field names the behaviour when relinking tables from acces is unknown!
Recently I've migrated a BIG access database (over 70 tables) to Mysql, leaving access frontend for form record editing, queries and reports and I've experienced some problems.
These are the ways I solved them :
If you have/plan-to-use referential integrity, use innodb tables that support foreign keys. This is possible from some latest versions of Mysql 3.23.x, but they have to be explicit configured. Using Mysql 4 there are no problems since InnoDB engine is active by default.
Using v. 4.0.x can lead to some problems with MyODBC and MS access.
So use, if possible, Mysql >= 4.1.10 and MyODBC >=3.51.11-2.
Use dbtools pro freeware edition to migrate the entire database in one shot.
It ca migrate also relationships and indexes and automatically normalize field/table names to a standard form (removes spaces, junk chars, etc.). This is the best tool i've tried to migrate access db.
Take a look at fields type for those that were currency in access. These are sometime converted as double, but this can cause an error when updating fields from an access form (record has been locked by another user....). Change them to decimal(20,4) or similar precision.
Be sure that a primary key is present in each table before linking tables from access. If not, add an integer autoincrement field (best in first position) and make it primary key.
Add a Timestamp field defaulting to now() to each table. This can solve the #deleted# field problem when inserting new records from a form.
Be sure that the fields used in queries are indexed. e.g. If a query uses an ORDER BY F1, F2, F3, make a single index for all three fields. If there's a filter, index the filtering field/s This can seem obvious to most people, but I read much about slow access queries in newsgroups!
For details about SQL commands in Mysql, there's the great mysql documentation (eg. RTFM).
How to find :
DBTOOLS ->
http://www.dbtools.com.br/
MYODBC 3.51.11-2 ->
http://www.peterharvey.org/Downloads/MySQL/MyODBC/
Regards,
Claudio