tranferText to a CSV directly from sql statement- Possible? (1 Viewer)

[Happy YN]

I have a SQL statement built on the fly. I want to transfer the data of that SQL to a new CSV file.
So far I have had to create an actual query to dump in the data and then transferText to a CSV.
Is there any way I can miss out the middle step? The transferText method seems to demand an actual table or query but using a query has its drawbacks - I have to keep on deleting it after use etc - admittedly not the end of the world but I want to keep the procedure as neat as possible.
Thanks

 

[fuzzygeek]

Not necessarily the answer you want. Use an undefined query that "disappears" after use. Of course to do this you would have to use DAO. I don't know if it is similiar in ADO, I never tried it.

From Access help in VBA:

If the object specified by name is already a member of the QueryDefs collection, a run-time error occurs. You can create a temporary QueryDef by using a zero-length string for the name argument when you execute the CreateQueryDef method. You can also accomplish this by setting the Name property of a newly created QueryDef to a zero-length string (""). Temporary QueryDef objects are useful if you want to repeatedly use dynamic SQL statements without having to create any new permanent objects in the QueryDefs collection. You can't append a temporary QueryDef to any collection because a zero-length string isn't a valid name for a permanent QueryDef object. You can always set the Name and SQL properties of the newly created QueryDef object and subsequently append the QueryDef to the QueryDefs collection

 

[Happy YN]

With a zero length string I would have trouble defining it in the transfertext method and anyway it would still be a 2 stage process.
Thanks for the suggestion anyway!