• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2963
  • Last Modified:

VBA code to export an MS Access query to a text file

Hello Experts,

I need help with the VBA code to export a query run in access to a text file.  I have little experience with VBA and don't even have a clue where to start.  

I hope someone can point me in the right direction.

Thank you.
0
elletter
Asked:
elletter
  • 4
  • 2
1 Solution
 
Jacques Bourgeois (James Burger)Commented:
Assuming that your VBA code is in Access:

DoCmd.TransferText acExportDelim, "", "YourQueryName", "Filename with path", False, ""
0
 
elletterAuthor Commented:
Sorry I should have been a bit more specific.  The query contains 5 fields and I need to export them to a text file in the format below

ASE-bab                 111.42    J384229  17016.2126                        33299.82

There are predetermine spaces between the fields.  I want to create this output for EDI purposes.

Hope i was able to be more clearer.  

Thank you.
0
 
Jacques Bourgeois (James Burger)Commented:
Do you know how to create specifications to import a fixed width file?

This is something you might have done if you imported files in a format similar to the one you seek to create.

Also, tell us on which version of Access you are working.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
elletterAuthor Commented:
I'm not familiar with that and I'm using ACCESS 2010.

Thanks for the help.
0
 
elletterAuthor Commented:
i got the following code and it's working so far, now i just need help to move to the next line were i need to output 3 more fields.

Do Until .EOF
        strData = ![identifier] & Space(20 - Len(![identifier])) '1 - 20
        strData = strData & ![Value] & Space(20 - Len(![Value])) '21-40
        strData = strData & ![B/Lno] & Space(20 - Len(![B/Lno])) '41-60
        Print
       
        Print #intFileNum, strData
        .MoveNext
        Loop
        End With
0
 
elletterAuthor Commented:
This code solved my problem adding vbNewLine completed everything i need it.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now