Right now Im converting my MS Access database to use MySQL as the engine. Im exporting my tables to the MySQL db and then relink them. This creates a few problems:
1) When I relink a table I have to delete the old one, and link a new table with the same name from the MySQL db. This means that after Im finished I would have to redesign my whole relationship connections. Is there a way/app to switch an existing table with a linked one without deleting it 1st?
2) MS (fucking) Access wont export default values, "required" properties (=not null in SQL), and comletely trash all format and input mask properties. Im using EMS MySQL GUI Manager to fix the required and default properties and then I have to rewrite the whole formatting properties. Is there a better way/app for doing this?
3) Ms Access exports auto numbered columns as regular int columns (why?). Im changing the properties wo auto-inc with the GUI manager but I forsee problems with adding new lines. Is there a way around this?
The best way around your problem is to get away from the point-and-click and understand what's going on in the backend. I would create the tables like I expect them to be from SQL, define the relationships, and set the default values BEFORE I pump the data to them. You can always link TAB1 that lives in mysql to MYSQL_TAB1 in Access.