calling all Access or SQL gurus

[siftings81]

I've been given the task of retrieving data from a lot of MS Access files and inserting them into a Mysql database. It was suggested that I export each table to a text file and insert that into the mysql database.

That works fine, but after doing it 50 times, I need to find a way to automate the process.

I wrote a java class that will cycle through each Access file, executing a query to select all the rows in the table I need and insert them into a text file. The problem is that the sql query I use works in mysql but not access:

SELECT * into OUTFILE 'C:\\TableDump.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' FROM 8227;

I get the following when I run it:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Query input must contain at least one table or query.

8227 is the name of table I want.

I'm not too familiar with Access, but I thought I could run valid SQL queries?

 

[KLin]

Access cannot dump straight to a flat file from a query statement. You would need to use VBA code to export a query. Look up docmd.transfertext in the access help file.

 

[siftings81]

Thanks for your reply.

Got it to work for the current open DB, but is their any way to get it to loop through a directory and export the one table of all the Access files?

 

[Crusty]

If you have access to a linux box there is a set of tools called mdbtools that will take an access database and dump the data to a text file in csv format. You can then import that directly into mysql, or use a perl script to read and generate queries.

 

[KLin]

Originally posted by: Crusty
If you have access to a linux box there is a set of tools called mdbtools that will take an access database and dump the data to a text file in csv format. You can then import that directly into mysql, or use a perl script to read and generate queries.

Now there's something I didn't know. Thanks :thumbsup: