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

Posted on 2014-03-05
Medium Priority
Last Modified: 2014-03-12
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.
Question by:elletter
  • 4
  • 2
LVL 40
ID: 39907991
Assuming that your VBA code is in Access:

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

Author Comment

ID: 39908016
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.
LVL 40
ID: 39908109
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.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.


Author Comment

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

Thanks for the help.

Accepted Solution

elletter earned 0 total points
ID: 39910839
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 #intFileNum, strData
        End With

Author Closing Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question