Automated Export From Access To TXT

[GFB45202]

This is a great forum, and I am truly happy for all the help people get.

I have a vexing request. I need to output a lot of data from a query into
txt files based on the data in one of the columns of data. It's important
that these be txt files since another program is going to scoop them up and
use them as a data source.

I would normally hard code this, but the data in HEADER2 will determine the
name of the file. The data is dynamic, and there is no way I can foresee
what will appear in that column.

Provided data is not the real data, but is useful for describing what is
needed.

THANK YOU!

------------------------------------

Query Results

HEADER1 HEADER2
ABC OHIO
ABC UTAH
DEF UTAH
XYZ TEXAS
LMN TEXAS
GHI TEXAS


Desired output will be three files.

File 1 will be OHIO.txt.

File will contain:

HEADER1 HEADER2
ABC OHIO


File 2 will be UTAH.txt.

File will contain:

HEADER1 HEADER2
ABC UTAH
DEF UTAH


File 3 will be TEXAS.txt.

File will contain:

HEADER1 HEADER2
XYZ TEXAS
LMN TEXAS
GHI TEXAS

 

[Tom van Stiphout]

On Mon, 21 Sep 2009 17:55:01 -0700, GFB45202

You'll need to write some VBA code along these lines (off the top of
my head)
dim sql as string
sql = "select distinct Header2 from myQuery"
dim rs as dao.recordset
set rs=currentdb.openrecordset(sql, dbopensnapshot)
while not rs.eof
forms!myForm!myTextbox = rs!Header2
DoCmd.TransferText acExportDelim, , "myQuery2", "c:\" & rs!Header2 &
".txt", True
rs.movenext
wend
rs.close

This code assumes:
* your main query name is myQuery.
* you have a (possibly hidden) form named myForm with a textbox named
myTextbox.
* you have a query named myQuery2 like this:
select * from myQuery where Header2=Forms!myForm!myTextbox

This hoopla is necessary because unfortunately DoCmd.TransferText does
not take a sqlstatement as the TableName argument. So we create a
query that will select the needed data.

-Tom.
Microsoft Access MVP