Use DAO and a blank query you've created and saved:
Dim qdf As DAO.QueryDef
Dim strSQL as String
strSQL = "SELECT * FROM tblWhatever ORDER BY fldYourMomma"
'sets the query
Set qdf = CurrentDb.QueryDefs("qryYouMade")
qdf.SQL = strSQL
DoCmd.OutputTo acOutputQuery, "qryYouMade", acFormatText, "C:\text.txt", True
'clean up & release resources
Set qdf = Nothing
Thanks alot, it worked. As note to future readers I would add that you need to have the microsoft DAO library pluged in and I also changed the export method to DoCmd.TransferText so they both work.
TransferText works in a VERY odd way i.e. if the results returned by query are relatively small then everything is ok - it creates a text file and puts the data inside. BUT. if the results are large then it gives me an error '3011' ( object fname not found by JET) and the intriguing part is that the file indeed disappears even if it existed before - without warning or anything. Any advise on how to fix this?
Regarding OutputTo - it warks regardless of the size of queries resultset, but it only outputs file in a certain format i.e. delimited by | and _ , how can I change this to tab delim?