Solved

vba access query export to text

Posted on 2014-12-17
6
297 Views
Last Modified: 2014-12-23
This should be so straight forward.  I'm trying to use vba to export an access query to text 'with formatting and layout.'  If I do this manually (External Data ribbon > Text Export), it only allows a certain number of rows.  I can create an export spec but since there are so many different layouts I need, I would have to create a multitude of them.  I'm really looking for something which mirrors a generic 'with formatting and layout' export.  Looks like this:
--------------------------------------------------------------------------------------
|    GB_ENT_SKEY     |    AUD_INSRT_ID    |  AUD_INSRT_TMSTP   |    AUD_UPDT_ID     |
--------------------------------------------------------------------------------------
| 16389278           | HIXRC_ETL          | 7/24/2014 4:02:44  |                    |
--------------------------------------------------------------------------------------
| 16389318           | HIXRC_ETL          | 7/24/2014 4:02:44  |                    |
--------------------------------------------------------------------------------------

Is there something simple without acExportFixed?  . . . without Export Spec?
         DoCmd.TransferText acExportFixed, "Export Spec", "qSel_xxxx", "yyyyy.txt", False, ""
0
Comment
Question by:CFMI
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
ID: 40505740
This is much more complex that perhaps you'd like but it's an excellent tool to have in the shed
'declare an object to be a filesystem object
Dim fs As Object
Dim BatFile As TextStream
Set fs = CreateObject("Scripting.FileSystemObject")
Set BatFile = fs.CreateTextFile("c;\temp\test.txt", True) 'some valid path
BatFile.WriteLine ("I am Open")
BatFile.Close

Set fs = Nothing
Set BatFile = Nothing

Open in new window


Now, you can knock together as many
BatFile.WriteLine ("some string")
as required

So, you can open a recordset, use the fields in each record to construct a string and then write it
Absolute granularity and control for you.
You also need to do all the work.

It's a trade-off
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40506354
Wouldn't it be easier to export a .csv file with a header row?

Text files are not useful as reports because of the standard use of non-proportional fonts so nothing lines up.  Plus, putting all the formatting characters in the text file makes it more difficult to be computer readable so the formatted files are pretty much good for nothing.
0
 
LVL 1

Author Comment

by:CFMI
ID: 40507118
I'm not that good with vba but if I read correctly, I'd have to create an export spec for each query I need to output.  I don't mind some complexity but would need more guidance through it.

That said, I did test this approach which will work just fine.  
  DoCmd.TransferText acExportDelim, , "qSel_xxxx", "yyyyy.txt", False, "" 

Should we close this chapter and I'll thank you profusely?  Or are there other ideas?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40508295
Fixed width files are a PITA and you have only the two options.
1. an export spec for each DIFFERENT format.
2. Learn how to use functions to coerce the columns to be fixed width in the query you use for export.

Select  textfld1 & Space(15 - Nz(Len(textfld1),0)), Format(datefld1, "yyyymmdd"), Format(numericfld1, "000000000.00"), etc.

This is just three examples that show how to do it with the three basic data types.  The first example uses the Nz() and Len() function to get the length of the contents of textfld1 and ASSUMING you want the output to be 15 characters subtracts the length from 15.  So if textfld1 = "abcd", the length would be 4 and so 11 spaces would be appended to the end.  You just have to use the correct length for each column.  Dates are generally exported as yearmonthday when fixed length files are created and numeric fields are zero filled.  So in this case, the output field is defined as 9.2.  If you need something different, adjust the number of zeros and include/omit the decimal point.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
ID: 40508301
Should we close this chapter and I'll thank you profusely?  Or are there other ideas?
CSV or XLS work for me, and are easy to crank out.

But you haven't said what the end purpose of these exports is.
And that kind of matters a great deal
0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 40515593
PatHartman,  I'm now getting your message that really the output requires some kind of export specification with either option 1 or 2.  Since there were so many queries, I cajoled the recipients to accept a docmd.transferspreadsheet method.

Nick67,
The purpose was to accommodate and auditor request.  I think they're satisfied with my deliverable and so . . .

THANKS TO ALL, AND TO ALL A GOOD NIGHT. . . . and Merry X-Mas and Happy New Year!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

932 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

11 Experts available now in Live!

Get 1:1 Help Now