Solved

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

Posted on 2014-03-05
6
2,325 Views
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.
0
Comment
Question by:elletter
  • 4
  • 2
6 Comments
 
LVL 40
ID: 39907991
Assuming that your VBA code is in Access:

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

Author Comment

by:elletter
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.
0
 
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Thanks for the help.
0
 

Accepted Solution

by:
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
       
        Print #intFileNum, strData
        .MoveNext
        Loop
        End With
0
 

Author Closing Comment

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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now