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

elletter
elletter used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
Assuming that your VBA code is in Access:

DoCmd.TransferText acExportDelim, "", "YourQueryName", "Filename with path", False, ""

Author

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.
Top Expert 2015

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.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

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

Thanks for the help.
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

Author

Commented:
This code solved my problem adding vbNewLine completed everything i need it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial