Adding Header & Trailer Records to TransferText Exports

[whdyck]

I'm using MS Access 2003.

I have code that exports the results of a query to CSV file using DoCmd.TransferText. However, since this export file will be used in an EDI transmission, I also need to prepend header records and append trailer records to the extract.

Can/should this be done within Access? What's the best approach?

Thanks for any help you can give.

Wayne

 

[DJkarl]

Can it be done, yes. Should it be done, sure why not. If I needed to do what your are trying I would use the Open and Close file command along with a recordset object and export the CSV myself. Exporting the header and trailer at the appropriate time. If you don't want to write your own CSV export function, then you could open the file after the face and insert your header at the beginning and append the trailer at the end. If the files are small I might recommend reading the entire file into a string, append the header and trailer to the string, then write that out to a new file.

 

[vbaInet]

* Create a table
* The first record of the table will contain your header and anything after that will be the trailing record(s)
* Ensure you have a Number field in the table where 1 is for the header and so on.
* Create two queries based on the temp table, one to pull the header row only (i.e. where ID is equal to 1) and the other to pull the other records, i.e. where the ID is greater than 1
* Use a UNION to join the header query, your original query/table and the query of trailing records
* Export this query