Query export to .txt delimited

[dubiic]

Hey guys!
I have spend last 8 hours trying to get this done with just one button, but without success. Searched all over in previous posts, but didn't get clear answer to what i am looking for.
Now I am max confused. I cant understand the difference between OutputTo and TransferText in access2010
So i need to export query to txt file with semicol as delimiter.
Thank you very much.

 

[Rank Am]

I have had a similar problem in the past where I had to export data from an access database for daily upload into a third party database using specific delimiters in a text file.

I don't think that it is possible to specify the delimiter from access VBA (I maybe wrong) but it is possible from XL VBA. (I know the OutPut to method of the DoCmd object gives a formatted text file).
The workaround I use is to create a recordset using the table or query and then instance an excel workbook, worksheet and range from access and use the copyfromrecordset method to dump the data into excel.

The code then saves the workbook as a textfile with delimiters specified in the code. you can find code that can be ammended to save an XL workbook as textfile at this link. This is the code that I used as the base for my export class.

http://www.cpearson.com/Excel/ImpText.aspx

Regards

Jon

 

[Galaxiom]

Use External Data > Export > Text.
Select the destination filename > OK
Choose Advanced to set the delimiter to semicolon.
Save the Export Specification.

Use TransferText and include the saved Export Spec name as the SpecificationName argument.

 

[dubiic]

Galaxion solution worked like a dream. Really really simple and without involving excel.
Thanks guys for the effort!