Solved

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

Posted on 2014-03-05
6
2,439 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

770 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