CODEWITHSUNDEEP
mysqlsqlms-accessvba
Rob
Rob

Reputation: 69

Exporting MySQL table to .csv file

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?

Upvotes: 1

Views: 1037

Answers (1)

Barranka
Barranka

Reputation: 21057

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.

Upvotes: 3

Related Questions