From multiple queries, how to script a .txt file

[znott2]

I have about 20 MS Access 97 queries. Instead of openning each one
manually and seeing what's wrong (error reports), I would like to have
each query sent to a .txt file. I've played with Transfer Text a bit,
but here's my ultimate goal if you have any suggestions.

1. delete contents, not file, of target .txt file from previous date
(C:/ErrorReport.txt)

2. Add the following text before running first query at
C:/ErrorReport.txt
Query 1 results:
3. append results of Query1 to C:/ErrorReport.txt
4. add new line
5. Add the following text before running second query at ....
6. append results of Query2 to C:/ErrorReport
7. add new line

all the way to last query.

What direction would you recommend?

TIA

[pietlinden]

for 1., I would just use a Kill statement to get rid of the file.
What's the difference?

I would probably loop through the querydefs, open each into a
recordset, and then output them all to a single file. Either that or
output them all using TransferText and then use DOS commands to copy
the intermediate files to the final file. Otherwise, you'd do
something like...

'delete text file
if Dir(strFilePath)<>0 then
Kill strFilePath
end if

open your text file
'see OpenFile...

loop through the querydefs collection... something like
dim qdf as querydef
dim rs as dao.recordset
for each qdf in dbEngine(0)(0).Querydefs
set rs=qdf.OpenRecordset
write #1 vbcrlf 'put in the blank line...
do until rs.EOF
'write your fields values to the text file
for intCounter = 0 to qdf.fields.count-1
if intCounter = 0 then
strTemp=qdf.fields(intcounter)
else
strTemp=strTemp & cDELIM & qdf.fields(intCounter)
next intCounter
put #1, strTemp
rs.close
.....

Does that get you started?
Look up Open, Write/Put, QueryDefs, and that should get you started...
you have to loop through the querydefs, open them one at a time into a
recordset, loop through each field, write that to a string, and then
write that string to your text file. Lather, rinse, repeat. Go to
next query...

[Chuck Grimsby]

First off, #1 is a bit silly. "delete contents, not file"? What would
be the purpose of that. Why not just delete the file and create a new
file with the same name?

For the rest, try Dimitri Furman's TextExport class:
http://www.mvps.org/access/modules/mdl0058.htm or
http://www.users.cloud9.net/~dfurman.