Export Sql Query To Csv File

If you want to export the query result to csv file.

Here is the code-



Sub export_query_as_csv()
DoCmd.SetWarnings (False)
Dim importtable As String
Dim exportfile As String
Dim temp_table_name As String
temp_table_name = Format(Now(), "dd_mmm_yyyy_hh_ss")
' first we will create a temp table using query and then we will export the table to csv and at end we will delte the temp table
' create temp table
DoCmd.RunSQL "SELECT * INTO " & temp_table_name & " FROM sales_detail WHERE [rep name] = 'a'"
' new table name
' complete path with csv file name
exportfile = "C:\Documents and Settings\user\Desktop\acess tutorials\28 dec 2011\rep_name_a.csv"
' check if the any csv file aready exists with same name
If Dir(exportfile) <> "" Then
MsgBox "File already exists"
Exit Sub
End If
' convert table to csv
DoCmd.TransferText acExportDelim, TableName:=temp_table_name, FileName:=exportfile, HasFieldNames:=True
'once export is completed we will delete the table
On Error Resume Next
'close the table if open
DoCmd.Close acTable, temp_table_name, acSaveYes
On Error Resume Next
' delete table table1 if exists
CurrentDb.TableDefs.Delete temp_table_name
DoCmd.SetWarnings (True)
End Sub

Download Access Database