Home / server / Questions / 81482
In Process
Jason S
Asked: 2009-11-05 13:16:56 +0800 CST

converting Access .mdb file to MySQL database tables

  • 772

I have an .mdb file that a coworker wants me use to convert to tables on our MySQL server. I'm familiar with MySQL, but have very little experience with Access (nor do I really want to learn).

Any suggestions on where to find out the information I need?

mysql

6 Answers

  1. MS-Access works very well with ODBC to MySQL.

    If you have a DSN connection set up, you can import all the tables using the import from external data source. The exact menu commands vary from Access 2003 to Access 2007.

    Or you can uses linked tables, which give you a live connection to MySQL tables.

    In both cases you'd preserve the meta-data (column types).

    I don't recommend using comma separated values or fixed width. You will waste a lot of time re-establishing the meta data (what fields are text, which are dates, etc)

    • 3

  2. If your looking to convert JUST the tables, then I would suggest you export them as CSVs and then import them to MySQL from the CSVs.

    • 2

  3. This is another option - Makes it pretty painless.

    http://www.bullzip.com/products/a2m/info.php It will also convert / transfer not just the tables but the records as well.

    • 1

  4. There are a number of tools that will handle Access->MySQL transfers. I like Navicat, but you can download the free MySQL migration toolkit and it works fine with Access.

    Access to MySQL is pretty much painless.

    • 1

  5. You can use the converter from the mysqlguitools package to import the data directly to mysql

    • 0

  6. I am doing the migration from a MDB database to MySQL doing some transformations and data validation on the way and the best tool for me right now is Pentaho Kettle (Community Edition). It's free and you can load the MDB database as input, browse the table, make transformations on the data and output as CSV, SQL, TXT, ...

    • 0