Access DB5 to MySQL automatically

Question

I have a problem, and was hoping I could rely on some of the experience here for advice and a push in the right direction. I have an MS Access file made by propietary software. I only want to take half the columns from this table, and import into new(not yet setup)mysql database.

I have no idea how to do this or what the best way is. New data will be obtained each night, and again imported, as an automatic task.

One of the columns in the access database is a url to a jpeg file, I want to download this file and import into the database as a BLOB type automatically.

Is there a way to do this automatically? This will be on a windows machine, so perhaps it could be scripted with WSH?

Shephard

Answer 1

This is a bash script linux example using mdbtools for automatic extraction and import from a mdb file to mysql.

#!/bin/bash

MDBFILE="Data.mdb"

OPTIONS="-H -D %y-%m-%d"
mdb-export $OPTIONS $MDBFILE  TableName_1 >  output_1.txt
mdb-export $OPTIONS $MDBFILE  TableName_2 >  output_2.txt

mdb-export $OPTIONS $MDBFILE  TableName_n >  output_n.txt

MYSQLOPTIONS=' --fields-optionally-enclosed-by=" --fields-terminated-by=, -r '
mysqlimport $MYSQLOPTIONS -L -uuser -ppasword database output_1.txt
mysqlimport $MYSQLOPTIONS -L -uuser -ppasword database output_2.txt
mysqlimport $MYSQLOPTIONS -L -uuser -ppasword database output_n.txt

You can use some others mysqlimport options: --delete: to delete previous Data from the target mysql table. --ignore: ignore duplicates --replace: replace if a duplicate is found

It's not a windows solution but i Hope it helps.

Dimond

Thankyou for your answer, Can you clarify a bit more, would I replace TableName with all the tablenames I want? Would it make sure that data would not be imported twice? – Guillermo

I've edit my post. But yes, you need to replace TableName_n with all the tablenames you want. You have some mysqlimport options to prevent duplicate data. – Dimond

Why would you use MDBTools on a Windows box when you've already got genuine Jet installed on it (Jet is a component of Windows, because it's used as the data store for Active Directory)? – Danyel

As I said before this is a bash script LINUX example. I'm in a LINUX box. "It's not a windows solution but i Hope it helps." – Dimond

Answer 2

http://www.dbtalk.net/mailing-database-mysql-win32/what-quickest-way-convert-access-136837.html

Search for Kofler (He wrote a german Book, where Part of it was a mdb2sql converter)

Here is a newer edition. http://www.amazon.de/Definitive-Guide-MySQL/dp/1590595351/ref=sr_1_3?ie=UTF8&s=books-intl-de&qid=1225197012&sr=8-3

Hugues