Using a PHP script to do a MS Access to MySQL data move....

[pthurmond]

I am trying to figure out how to migrate data from a Microsoft Access mdb file to a new MySQL database. Not all of the fields will be Migrated, but most of them will be. I am pulling the data from two different tables and some of the fields that the data will be moving to will not have the exact same name as in the old db.

The question I have is can I write a php script to move it for me. What I would like to do is just put the mdb file on the server and then use php db hooks to open it and grab the data and then insert it into the new database. I am currently unaware of any way to do this (mainly because I don't know of a way to open mdb files with php). Does anyone have any ideas? Can anyone point me in the right direction or to a better solution? The database has couple thousand records to move, so I can't just manually move them over.

Thanks,
Patrick

[roopurt18]

I know you can connect to the Access DB through PHP, but I'm unsure of how exactly how that is done, so I have little advice to offer in that department.

For the MySQL portion of it, I would create three .sql files.  The first would be to establish and create your destination DB; all of it's tables and fields.  The second would be to create a MySQL mirror of the Access DB with all of the tables prefixed by msa_.  The third would be to INSERT ... SELECT from the msa_ tables to the final tables.

The reason I recommend this approach is because you do not plan to need all of the data now, but that may change in the future.  It is much easier to insert data across MySQL tables than it is from access to MySQL.  So if it turns out you do need additional data later, you'll have easier access to it then and you'll only have to deal with the Access -> MySQL nightmare once.

The other reason I recommend this approach is because of a similar situation where I work.  We have a server software product that uses dbase tables and must continuously import data into a MySQL driven web application.  The original developers of the web app, who are no longer with the company, designed many of the MySQL tables to be mirror images of the dbase tables.  The problem is dbase != MySQL and those tables are [i]not[/i] set up to take advantage of some of the handy features MySQL has to offer.  I would have much rather preferred an approach where the dbase data is imported into mirror MySQL tables and then from those tables imported into the "real" MySQL application tables so as to minimize data repitition.

After enough time elapses that you are sure you no longer need the msa_ tables, you can just drop them or export them and save them as backups.

[mlin]

About connecting to access. Is it even neccessary? Access can export csv files, and you could work with them from there