Exporting MySQL table to .csv file

Question

This is my first day with VBA. I am trying to export a .csv file in MS Access using MySQL query in VBA. I have tried using this code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String

Set db = CurrentDb

sql = "SELECT wo_id FROM WorkOrder"

Set rs = db.OpenRecordset(sql)
DoCmd.TransferText acExportDelim, , "rs", "C:\export.csv", True

rs.Close
Set rs = Nothing

which gives me:

Run time error '3011':

The Microsoft Office Access database engine could not find the object 'rs'. Make sure the object exists and that you spell its name and path name correctly

MORE INFO

I have a MySQL table called WorkOrder (I would like to pull data from here).

What am I missing?

stackoverflow.com/questions/14700192/…

Tim Williams
– Tim Williams

2014-01-14 22:55:34 +00:00
Commented Jan 14, 2014 at 22:55 — Tim Williams
– Tim Williams, Commented Jan 14, 2014 at 22:55

Barranka · Accepted Answer · 2014-01-14 23:08:25Z

You can only export MS-Access objects (tables or queries) using DoCmd.TransferText. If you have a query called qryOutput you can export it:

DoCmd.TransferText acExportDelim, , "qryOutput", "C:\export.csv", True

You could create the query on runtime (using db.CreateQueryDef), export it and delete it.

If you are working with MySQL, maybe it is easier to export the data directly from the command line:

c:\> mysql -h YourHost -u YourUser -pYourPassword dbYourDatabase -e"SELECT wo_id FROM WorkOrder" > c:\export.txt

This will create a tab-separated text file with the result of your query (including headers).

On a Unix-like command line you could convert this output on-the-fly to a comma separated text file using sed:

$ mysql [connectionParameters] -e"select..." | sed 's/\t/,/g' > export.csv

I don't know if there are any pure-windows command line utilities to do this. I use Cygwin for this kind of things. If you want to use it, be sure to install sed using Cygwin Setup program.

Hope this helps.

Collectives™ on Stack Overflow

Exporting MySQL table to .csv file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related