Microsoft Access VBA Export all Query to Text File

This Access tutorial explains how to use Access VBA to export all Query to Text file.

You may also want to read:

Microsoft Access Export all Queries to Excel

Access VBA DoCmd.TransferText Method

Export Access Objects to Text (non VBA)

There are two kinds of export that we can use to export an Access object to Text file.

Assume that we want to export Query1 to Text. Right click on the Query > Export > Text File

 

This page has a check box Export data with formatting and layout which determines the underlying VBA Method to be used.

If you check the box Export data with formatting and layout, you will end up getting the below ugly result.
For this option, you are using DoCmd.OutputTo Method to generate the result. We are not going to talk about this Method in this tutorial but you can click on the hyperlink to my another post.

 

If you leave the box unchecked, there will be more customization options, this is to use DoCmd.TransferText Method to generate the result.

 

In the next step, select delimit by Tab, select Text Qualifier to none (to prevent double quote around each data)

 

Now you get the Text file delimited by TAB.

Access VBA Export Query to Text File (one query)

As explained above, there are two kinds of export, but we do not want to ugly “formatted” result, therefore we are going to use the customizable DoCmd.TransferText Method, and use the same options above.

Now insert a Module, paste and run the below Procedure. It will export Query1 to the C:\test\ folder.

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, TableName:="Query1", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Go to C:\test\ and open the text file.

The file looks almost perfect, except for the double quotes around each header name and data. Unfortunately, DoCmd.TransferText Method does not have an argument to set the Text Qulifier to none.

To remove the double quotes, repeat the non-VBA steps to export a Query, and click on the Advanced button in the below step.

 

Click on Save As > OK

 

Now add one more parameter SpecificationName to the Procedure.

Public Sub export_query()
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:="Query1", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

Then you get the below result that has removed the double quote. The Specification can be used for other Queries as well.

Access VBA Export All Query to Text File

Public Sub export_query()
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:=qdf.Name, FileName:="C:\test\" & qdf.Name & ".txt", hasfieldnames:=True
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
End Sub

Access VBA Export specific Query to Text File

Add an If condition in the For Each Loop to control which name should be or should not be exported using INSTR Function.

The below example only exports Query name that contains “2”   (Query2)

Public Sub export_query()

  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef

  Set db = CurrentDb()
  For Each qdf In db.QueryDefs
    If InStr(qdf.Name, "2") <> 0 Then  'If query name contains 2
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="Query1 Export Specification", TableName:=qdf.Name, FileName:="C:\test\" & qdf.Name & ".txt", hasfieldnames:=True
    End If
  Next qdf
  Set qdf = Nothing
  Set db = Nothing
    
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff835958.aspx