Having troble exporting a text delimited file

[itm]

Hi,
I have an export rutine in my VBA code as follows that works great:
rs2.MoveFirst 'Moves to the first record

'This loops through output the dat to text files
While Not rs2.EOF
DoCmd.TransferText transfertype:=acExportDelim, _
specificationname:="Fi707sp", _
tablename:="TempHd", _
filename:=fname, _
hasfieldnames:=True


rs2.MoveNext
Wend
The problem is that when it go through this it depends on the spesification file being the same each time. The columns are dates that a cross tab query creates, and depending on the what the user picks for date selection; there are different columns of dates. But the spesification is based on certing dates for the collumns.
So how can I do this without the the spesification file. I know there are filescripting methods and free file, but I am not sure (a) how to use these for delimited text files and (b) how to have it go through and grab the number of columns I have at the time. Or if therer is a way to get the DoCmd to work.
Any ideas would really be appricated.
Thank you

[rpeare]

Do you have a specification name for every possible permutation of your data? If you do, is it strictly on the number of columns produced by the crosstab query? If it is you could do something like this:

if rst2.fields.count = 10 then
     'set your export specification to item x
elseif rst2.fields.count = 11 then
     'set your export specification to item y
elseif
     'and so on
endif

I seem to have a memory of crosstabs being very temperamental when you perform an export what I typically do is create a temporary access table then export that table, particularly if you are exporting a crosstab with *ANY* criteria at all. If your crosstab has criteria you may have to create the SQL statement in your code to get it to run without generating an Access table first.

[itm]

The problem is that the number and name of the heding will be different each time. However I solved this by writing a script that sends the file out by file scripting code and in therre I was able to loop through the fields; and this way I did not need a spec. sheet, and it had far better performance.
Thank you
itm