found this solution by someone else ---
Here is an 11 line solution. Granted this looks very long, but it is only because I included a lot of remarks about each step, and the fact that I chose a large table to work with. Should have picked something smaller to show you and example with, but this is a real part of one of my databases. This solution creates a temporary table from a query, exports it as a comma delimited text file, then deltes the temp table. Again, start to finish, only 11 lines of code (without remarks or error handling).
Private Sub CommandExportAgentInfo_Click()
Dim spec, tbl, path, filename, s As String 's=SQL Dim db As Object
'Create file export spec by doing a manual export, clicking the Advanced button, and after 'getting all you options the way you like them, click the SaveAs button 'and give your spec an easy to remember and relevant name
Set db = CurrentDb 'Need to utilize the Execute SQL spec = "tblAgent" 'The spec I set up is to save a table as a comma delimited, with no quotes around strings tbl = "tblTempExport" 'Pretty self explanitory, set up a variable to hold the name of the table you wish to export path = "C:Temp" 'Again just a variable to hold the destination directory filename = "tblAgent.txt" 'Or what ever you wish your filename to be 'My suggestion is to have these come from text boxes on a form. That way the person 'exporting the table can select their own destination and filename.
s = "SELECT tblAgentInfo.intID, tblAgentInfo.AgentID, tblAgentInfo.AgentNameL, " & _ "tblAgentInfo.AgentNameF, tblAgentInfo.SSN, tblAgentInfo.DOB, tblAgentInfo.StatusID, " & _ "tblAgentInfo.HireDate, tblAgentInfo.TermDate, tblAgentInfo.SalesID, tblAgentInfo.CableData, " & _ "tblAgentInfo.CableDataPassword, tblAgentInfo.KBLogin, tblAgentInfo.KBPassword, " & _ "tblAgentInfo.NTLogin, tblAgentInfo.NTPassword, tblAgentInfo.ISPLogin, tblAgentInfo.ISPPassword, " & _ "tblAgentInfo.intSupvID INTO " & tbl & " FROM tblAgentInfo;" 'Build your query then switch to SQL view, copy from there, and paste into VBA 'Also, using the & _ line continuation makes for an easier read
db.Execute s 'Found this little command here in DBForums. Using this you do not have to 'use the docmd.setwarnings False because Execute does not generate the messages 'like "you are about to create a table, you are about to insert 50 rows, etc...
DoCmd.TransferText acExportDelim, spec, tbl, path & filename 'The DoCmd.TransferText has several options for importing, exporting and linking 'various file and types and you can do delimited or fixed.
DoCmd.DeleteObject acTable, tbl 'Delete the temporary table
I hope this helps you out,
Dec 05 '11 at 06:58 AM