Export txt file with header/trailer record

[Lisa]

Hi

I'm new to using a newsgroup, but it seems to be a great
way to find solutions for problems. I'm sure this is an
easy one too.

I want to export a table from Access 2000 into a fixed
width txt file. This works fine using the
DoCmd.TransferText method. I need to put a header and
trailer record into the txt file as well. Is there an
easy way to put a header record into the first row and a
trailer record into the last row of the txt file? The
header and trailer records are completely different to
the rest of the data in that they have a different number
of columns and widths.

Thanks for any help.

Lisa

 

[John Nurick]

Hi Lisa,

There's no really easy way to do this. Here are some possible
approaches:

1) Use a report, with a single textbox in its detail section containing
an expression that builds up the entire fixed-width record. Likewise use
a single textbox in the report header and report footer sections.
(Alternatively, use multiple textboxes and position them very
precisely). Format them as Courier 12 pt. Make certain that there's no
blank space and that all the vertical dimensions in the entire report
are exact multiples of 0.125 inch. With care and luck, when you export
this report to a textfile you'll get what you need.

2) Export the file as now, but to a temporary file. Write the header and
footer data to separate files, then use the VBA Shell() statement to
execute a "DOS" COPY command to concatenate the three files into the one
you need; finally Kill the three temp files.

3) Write VBA code that uses file and recordset operations to create the
custom textfile.

4) If the records aren't wider than 255 characters one can use a cunning
union query to return the header, data and footer records, and use this
to feed a second query that gets them in the right order.

Post back here if you need more.

Hi

I'm new to using a newsgroup, but it seems to be a great
way to find solutions for problems. I'm sure this is an
easy one too.

I want to export a table from Access 2000 into a fixed
width txt file. This works fine using the
DoCmd.TransferText method. I need to put a header and
trailer record into the txt file as well. Is there an
easy way to put a header record into the first row and a
trailer record into the last row of the txt file? The
header and trailer records are completely different to
the rest of the data in that they have a different number
of columns and widths.

Thanks for any help.

Lisa

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.