Porting from MS Access

Question

I've recently been given a MS Access .mdb database file and asked to make it usable in a Linux system. What I'm looking for is a way to convert the Access database to an open-source database such as MySQL or PostGres.

I don't have MS Office, and it's a one-time project for a volunteer organization so I don't want to spend money if it's avoidable. I'm running Vista x64, and have a Linux virtualbox, so something usable in either one will be good.

Answer 1

Forunately you don't need MS Access to get data out of an Access database. While there are tools like MDBTools that will read .mdb files on Linux, I've found them to be unstable for larger, more complicated databases. The best way is to use ADO or ODBC on Windows to export the data from the .mdb database into a format you can import somewhere else (like csv).

I've had good luck using DBI with Ruby, or using ADO in a Ruby script.

Answer 2

There are two parts to the process

1) convert from Access database to scripts, there are some good options although you may need Access and potentially Sql Server too, to do this

2) Pick your target database - MySql would seem like a good fit, run it up on Linux or Windows and run the ddl and data in from the scripts.

Not sure how you can do this without Access though, do you have any idea what the schema looks like? If you do run into probs I could help out with step 1, but I don't have a Linux install at the moment.

Answer 3

Following links may be useful for you.

MySQL:

access migrate

read.php?65,27115,27115

PostgreSQL:

Microsoft Access to PostgreSQL Conversion

Converting from other Databases to PostgreSQL

Answer 4

Either MySQL or Postgres will work fine as a replacement data store for your access data. If your Access database contains Forms or Reports there really isn't a direct replacement in Open Source, and you'll either need to retain the Access front end and access the data using ODBC drivers, or code a new series of Forms or Reports in PHP or your favorite web scripting language.

Answer 5

I used the MySQL Migration Toolkit and it worked very well for tables, even creating a reusable script. It is free and simple to use.

Answer 6

I had the same problem now, and found this freeware to be exactly what I needed: http://www.bullzip.com/products/a2m/info.php

It extracted everything from the MDB file generated an SQL file I imported directly into my MySQL server.