VBA Export to CSV From SQL With Save-As in Access

Although not as common as exporting to Excel, some projects may have a need to export in .csv format. Here is my solution.

VBA Export With User Defined SQL Query

This example uses the QueryDef function. Basically, the user passes a SQL query in, the file dialog prompts for where to save-as and the export is completed.

'To use file-dialog, you must add a reference
'to Microsoft Office XX.X Object Library
'From the VBA code editor window
'Tools>References>Microsoft Office XX.00 Object Library
'''''''''''''''''''''''''''''''''''''''''
Public Sub exportQuery(exportSQL As String)
Dim db As DAO.Database, qd As DAO.QueryDef
Dim fd As FileDialog, iQdef As Variant

Set fd = Application.FileDialog(msoFileDialogSaveAs)
Set db = CurrentDb

'Check to see if querydef exists and delete to redefine

For Each iQdef In db.QueryDefs
   If iQdef.Name = "tmpExport" Then
      db.QueryDefs.Delete ("tmpExport")
   End If
Next iQdef

Set qd = db.CreateQueryDef("tmpExport", exportSQL)

fd.InitialFileName = "export_" & Format(Date, "mmddyyyy") & ".csv"

If fd.Show = True Then
    If Format(fd.SelectedItems(1)) <> vbNullString Then
        DoCmd.TransferText acExportDelim, , "tmpExport", fd.SelectedItems(1), True
    Else
        End
    End If
End If

'Cleanup
db.QueryDefs.Delete "tmpExport"
db.Close
Set db = Nothing
Set qd = Nothing
Set fd = Nothing

End Sub

How to Use Export Function

Call the above code in a button, other sub or where ever you have your call set up. Here is an example in a button:

Private Sub cmdTest_Click()
    Dim iQuery As String
    iQuery = "SELECT * FROM table"
    
    'Call Sub to Complete the Export
    Call exportQuery(iQuery)
End Sub