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

Access VBA DoCmd.TransferText Method 01

 

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

Access VBA DoCmd.TransferText Method 02

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.

Access VBA DoCmd.TransferText Method 03

 

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

Access VBA DoCmd.TransferText Method 04

 

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

Access VBA DoCmd.TransferText Method 05

 

Now you get the Text file delimited by TAB.

Access VBA DoCmd.TransferText Method 06

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.

Access VBA Export all Queries to Text File 01

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.

Access VBA DoCmd.TransferText Method 05

 

Click on Save As > OK

Access VBA Export all Queries to Text File 02

 

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 DoCmd.TransferText Method 06

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

2 thoughts on “Microsoft Access VBA Export all Query to Text File

  1. Hi, thank you for your post. Infortunately I received the message, that 2 parameter was aspected, and not enough received. This when I try to export a query to txt file withot the flag for formatting…

    1. Hi Sil, sorry I don’t get your meaning. You can send me some screenshots and the access file for me to take a look
      my email: scammera1@yahoo.com.hk

Leave a Reply

Your email address will not be published. Required fields are marked *