The Old Joel on Software Forum: Part 4 (of 5)

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.

map:
access|mysql
col1|col2
colX|null
colY|colY
null|colM
.
.
.
etc.

thanks.

bob
Tuesday, February 10, 2004

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.

Tom H
Tuesday, February 10, 2004

'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?

bob
Tuesday, February 10, 2004

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.

Egor Shipovalov
Tuesday, February 10, 2004

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.

e.g

Imagine in Access you have columns a1, a2, a3, a4 for table a and in mysql you have columns m1, m2, m3 for table m where m3 is a3+a4.

In Access run a query like (assuming the column concation operator is + if not substitute it)

select 'insert into m ( m1, m2, m3 ) values (' + a1 + ',' + a2 + ',' + a3+a4 + ')' from t ;

Running this should generate a SQL script which you can run in mysql. This is the best approach to take for importing data in such cases becuase you have complete freedom to define columns you want and how they are to be inserted in the destination db

Code Monkey
Tuesday, February 10, 2004