http://www.perlmonks.org?node_id=85771

kevin_i_orourke has asked for the wisdom of the Perl Monks concerning the following question: (database programming)

I saw the previous question about transferring data the other way (mySQL->Access) but I'm specifically trying to port my existing database to mySQL so I can rid myself of Access.

Just wondering if somebody has a script lying around to do something like this or if it's time to get to work myself.

Kev.

Originally posted as a Categorized Question.

Replies are listed 'Best First'.
Re: Transferring an Access Database to mySQL
by Asim (Hermit) on Jun 05, 2001 at 16:58 UTC

    I've used the ExportSQL Access macro with great success in the past. You can find a new version, 3.0, towards the bottom of http://www.rot13.org/~dpavlin/projects.html. I've not used the new version yet, but I will be doing so soon. A couple of minor caveats; it tends to work best with properly normalized data, and there are some libaries that need to be activated if you use it in Access 2K (the program docs will tell you what needs to be turned on)

Re: Transferring an Access Database to mySQL
by maverick (Curate) on Jun 06, 2001 at 02:38 UTC
    I (and our local Access god) had to do this for a project a couple of months ago. We weren't just cloning the database, we were also changing the structure. He exported the tables to csv files, I wrote some perl to do things like translate the date, time, and boolean formats from MS to MySQL.

    Because there isn't a perfect 1 to 1 mapping of data types between Access and MySQL, and we were restructuring the database, we found it easier just to csv export and then 'load data infile' into MySQL. You might also consider this route if your servers aren't network to each other.

    The downside is you have to do this one table at a time...it would suck if you had 100's of tables :)

    --/\/\averick

Re: Transferring an Access Database to mySQL
by Hero Zzyzzx (Curate) on Jun 05, 2001 at 21:18 UTC

    Warning! This answer doesn't relate specifically to perl!

    Why not try to rig something up with myODBC? I've successfully brought mySQL tables into access with myODBC and done updates on them.
    You can then do your work within access, if you're more comfortable with it.

Re: Transferring an Access Database to mySQL
by mrkoffee (Scribe) on Aug 31, 2005 at 19:09 UTC
    You might try feeding the output of MDB Tools to SQL::Translator. I know it has an Access parser (SQL::Translator::Parser::Access) included. I haven't used it to translate Access, but SQL::Translator has done well for me at converting to and from SQLite and MySQL.
Re: Transferring an Access Database to mySQL
by Anonymous Monk on Jul 30, 2001 at 01:10 UTC
    Kev, Please, download, http://www.hostcl.com/zip/exportar.zip This bas module was adapted to export all tables from databases to xxx.txt. Using xxx.sql itīs fine. Hope you can read some spanish! Change database name and name for text files as your requires. Call exportSQL funtion w/o parameters from any form in your vb app. The best way to give the txt file to mysql have been with phpMyAdmin. The file delete tables in your mysql database, re-create empty tables and transfer data for that table, one pass for table. Best from Santiago de Chile Charliej

    Originally posted as a Categorized Answer.