Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Transferring an Access Database to mySQL

by kevin_i_orourke (Friar)
on Jun 05, 2001 at 15:56 UTC ( #85771=categorized question: print w/replies, xml ) Need Help??
Contributed by kevin_i_orourke on Jun 05, 2001 at 15:56 UTC
Q&A  > 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.


Answer: Transferring an Access Database to mySQL
contributed by Asim

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 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)

Answer: Transferring an Access Database to mySQL
contributed by maverick

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 would suck if you had 100's of tables :)


Answer: Transferring an Access Database to mySQL
contributed by Hero Zzyzzx

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.

Answer: Transferring an Access Database to mySQL
contributed by mrkoffee

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.

Please (register and) log in if you wish to add an answer

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others drinking their drinks and smoking their pipes about the Monastery: (5)
    As of 2019-09-21 19:16 GMT
    Find Nodes?
      Voting Booth?
      The room is dark, and your next move is ...

      Results (273 votes). Check out past polls.