exporting query results as a .csv file

[snowmantle]

Hi,

I am using a .ade file, linked to an sql database.

The query is very much the same as in a .mdb, i just changed it a bit and put it in the sql database.

At the moment the query is displayed in a report for printing out, i was wondering how to do an export function that will put the results of the query into a .csv file and save it to a location.

Thanks for any help

 

[lupins46]

You just select the query (?view/sp?) in the database window and use File>Export to export it.

 

[snowmantle]

no sorry, the view in the sql database is used in Access, and i need to export the information in the report into a .csv using a button on a form in Access.

 

[PHV]

Have a look at the DoCmd.TransferText method (acExportDelim).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snowmantle]

I hadnt looked at the access dbase for a while, i have vba that builds an sql query to a variable called strWhere

and i do this:

DoCmd.OpenReport "ReportName", acViewPreview, , Mid(StrWhere, 6)

It opens the report and filters the query that is alrdy assigned to the report.

With DoCmd.TransferText i cant see a way of doing that...

Is my only hope in letting them export the entire table into a .csv and then have them use excel to filter it?

 

[snowmantle]

I figured another way of doing what i wanted with .adp.

This was for exporting report results that have been filtered.

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS, "P:\Export\Data" & Format(Now(), "mmddyyyy") & Format(Time(), "hhmmss") & ".csv", True
DoCmd.Close acReport, "ReportName"

This way i use the above code after opening the report with the sql filter from Access.

Export the results to excel and close the report.