Smart Access to Mysql
does anyone know of a tool that will map/import an Access db to a MySql db, allowing for an altered schema in the mysql db and preserving type information. i'm aware of some basic access->mysql importers (mostly just csv/sql dumps), but they don't seem to allow for, say, switching "col 4" in access table to "col1" in mysql table, removing "col 5", mapping "col x" to "colY", adding "colN", etc. if i provide the "map", are there any tools that can figure it out? in the last day or so i've written some scripts that work pretty well, but i was wondering if there was something more robust.
If you're going to change things around that much, just open a connection to each, SELECT from Access, INSERT into mysql. You didn't say how many tables but you should be able to write the SQL as fast as you could map columns in any other tool.
'tis what i've done, but i'm in design stage, so everytime i make a change to the mysql structure, i have to empty the table and change the sql and re-import. just wondering if 'twas an easier way, aye?
It's very easy to connect from Access to MySQL using MySQL's ODBC driver, and shuffle data between with VBA. Be sure to have a TIMESTAMP type column in every MySQL table, or Access may have trouble inserting records.
How about you construct an "insert statement generator query" for the access db. Save the results of the query in a file. Load the query in MySql and run it to insert the data.
Fog Creek Home