Avatar of CFMI
CFMIFlag for United States of America

asked on 

vba access query export to text

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, ""
Microsoft Access

Avatar of undefined
Last Comment
CFMI
SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of CFMI
CFMI
Flag of United States of America image

ASKER

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?
SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of CFMI
CFMI
Flag of United States of America image

ASKER

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!
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo