• Skip to main content
  • Skip to primary sidebar

Ryan McCormick

Dedicated Dad, Software Engineer and Lover of Coffee

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

February 16, 2015 by Ryan McCormick 1 Comment

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

Related

Filed Under: VBA Tagged With: access, export to csv, SQL, vba

Reader Interactions

Comments

  1. Miriam Hall says

    January 30, 2017 at 10:40 pm

    This worked perfectly. Many thanks.

    Reply

Leave a Reply Cancel reply

Primary Sidebar

Recent Posts

  • Force Quit Kill all Chrome Windows MacOS
  • SOLVED: Angular 6 CLI Karma Stuck in Single Run | Karma Stops Running
  • How to Manually Install Java 8 on Ubuntu 18.04 LTS
  • Remove VirtualBox from Ubuntu 16.04 Xenial
  • Clear all Node Modules Folders Recursively Mac/Linux

Recent Comments

  • KKV on Webstorm adding spaces between imports and braces | JavaScript and TypeScript
  • jusopi on Clear all Node Modules Folders Recursively Mac/Linux
  • Qaisar Irfan on Clear all Node Modules Folders Recursively Mac/Linux
  • mustafa on Remove VirtualBox from Ubuntu 16.04 Xenial
  • Pourya on How to Manually Install Java 8 on Ubuntu 18.04 LTS

Archives

  • May 2019
  • May 2018
  • April 2018
  • March 2018
  • January 2018
  • September 2017
  • August 2017
  • July 2017
  • June 2017
  • March 2017
  • December 2015
  • November 2015
  • July 2015
  • April 2015
  • February 2015
  • September 2014
  • June 2014
  • May 2014
  • April 2014
  • March 2014
  • February 2014
  • October 2013
  • August 2013
  • June 2013
  • April 2013
  • March 2013
  • February 2013
  • December 2012
  • October 2012
  • September 2012
  • August 2012
  • July 2012
  • May 2012
  • March 2012
  • February 2012
  • December 2011
  • November 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • August 2009
  • July 2009
  • May 2009

Categories

  • Angular
  • Angular 2
  • AngularJS (1x branch)
  • Computer Q&A
  • ES2015
  • Internet Marketing
  • Javascript
  • Job Interviews
  • Job Search
  • Karma
  • Laravel
  • Linux
  • Linux/Unix Tips
  • MacOS
  • Microsoft Access
  • Microsoft Excel
  • Microsoft Outlook
  • Microsoft Word
  • News
  • Node
  • Open Source
  • PHP
  • Protractor
  • Resume Writing
  • Spring Boot
  • SQL
  • Ubuntu
  • VBA
  • VBScript
  • VirtualBox
  • Web Development
  • Windows Tips
  • Wordpress

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Copyright © 2023 · Magazine Pro on Genesis Framework · WordPress · Log in