Using Report or Query to write ascii text data to a file

[RandyBlackburn]

I want to create a CSV file that will be used for MailMerge based on a query and/or report. Is there an easy way to do this?

I've written a routine to create a recordset and loop thru with Write #'s, but I'd like to understand what alternatives there might be.

Thanks,
Randy

 

[Steve101]

One high level alternative is to use the File, Export menu options, which is available when the query is open. You can select the 'Save As' type, CSV being one of the options.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[KenReay]

Hi

Or you can achive the desired effect using DoCmd.Transfertext

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RandyBlackburn]

Thanks, Ken and Steve.

Steve, how might I use the file / export programmatically?

Ken, I found TransferText, and tried it after I posted the problem. It works ok for me UNTIL I try to use the specification paramater. I keep getting error 3625 "The text file specification 'labels.txt' does not exist You cannot import or link using the specification".

I can't figure out how to set the specification name when I create the export. It seems to give it the same name as the extract file.

Here's what I'm doing:

I go into design mode for the query "Labels"
I select file\export... it asks for a file name and type.
I select filename: Labels type:microsoft wordmerge (.txt)
It creates the Labels.txt file with the exported data in csf format
it makes an entry in the schema.ini file for [labels.txt]

The files are placed in the My Documents folder with the mdb.

When I execute the line below, I get the 3625 error.

DoCmd.TransferTextacExportDelim, "labels.text", "Labels",
"labels.txt"

What am I missing? Also, is it kosher to modify the text in the ini file? (to add, remove columns, etc)

Randy

 

[KenReay]

Hi

I can understand your confusion, the User Interface to gain access to the Import/export specification is not intuitive

You need to use the File \ Export menu option, and you will noitce abutton 'Advanced', click that, and it will take you into another dialog box which includes the ability to create/maintain an import/export specification

The docmd.TransferText, is the programatic way of doing the file \ export thing, so Steve and I were effectively suggesting the same solution

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RandyBlackburn]

Ken,

I found the problem. I have Access2000, and when you select file/export, there is no hint of an "advanced" button on my system.

If I select the Microsoft Word Merge (*.txt) file type (which is what I did), it just writes the data as I described, with no wizard/advanced button, etc. The key is that it creates the schema.ini file which is used in conjunction with the .txt file that it creates for the mail merge, but it's not accessible by specification name.

However, if I select the *.txt; *.csv; *.tab; *.asc file type, then it starts the wizard, with the "advanced" button, etc.

Whew, it only took 3 days.... thanks for yours and Steve's help on this...

Randy