Can you connect to a MS Access database from Ruby running on a Mac?

Question

I'm pretty sure the answer is "no" but I thought I'd check.

Background:

• I have some legacy data in Access, need to get it into MySQL, which will be the DB server for a Ruby application that uses this legacy data.

• Data has to be processed and transformed. Access and MySQL schemas are totally different. I want to write a rake task in Ruby to do the migration.

• I'm planning to use the techniques outlined in this blog post: Using Ruby and ADO to Work with Access Databases. But I could use a different technique if it solves the problem.

• I'm comfortable working on Unix-like computers, such as Macs. I avoid working in Windows because it fills me with deep existential horror.

Is there a practical way that I can write and run my rake task on my Mac and have it reach across the network to the grunting Mordor that is my Windows box and delicately pluck the data out like a team of commandos rescuing a group of hostages? Or do I have to just write this and run it on Windows?

Answer 1

I had a similar issue where I wanted to use ruby with sql server. The best solution I found was using jruby with the java jdbc drivers. I'm guessing this will work with access as well, but I don't know anything about access

Answer 2

Why don't you export it from MS-Access into Excel or CSV files and then import it into a separate MySQL database? Then you can rake the new one to your heart's content.

Answer 3

Mac ODBC drivers that open Access databases are available for about $30.00

http://www.actualtechnologies.com/product_access.php is one. I just run access inside vmware on my mac and expore to csv/excel as CodeSlave mentioned.

ODBC might be handy in case you want to use the access database to do a more direct transfer.

Hope that helps.