Here is sample code to do what you are wanting to do.
I've had to hurriedly take out lines of code & rename variables etc - so this is not FUNCTIONING code.
It should give you a good starting point for your code, though.
Code:
Function YourFunctionName()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strLine, strVariableName, strVariableName2 as String
On Error GoTo Err_YourFunctionName
Set db = CurrentDb
strSQL = "Select * From [YourQueryname]"
Close #1
'Open the Text file for writing.
Open "\\ServerName\DirectoryName\FileName.txt" For Output As #1
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
Do While Not rs.EOF
'Get your field Values into a string variable - like this:
strVariableName = rs!FieldNameInTable
strVariableName2 = rs!NextFieldNameInTable
'etc . . .
'OR - just get everything into an strLine variable:
'strLine will be what you write to the Text File.
'This will be one row of data in your new Table.
strLine = rs!FieldNameInTable
strstrLine = strLine & vbTab & rs!NextFieldNameInTable
'Write a Code Loop to parse the text in strVariableName
'& create a separate line of tab-delimited text.
'Write the current Line to your text file
'and add a CarriageReturnLineFeed to create a New Line.
Print #1, strLine & vbCr
'Processing for current row that you create ends here.
'Loop and create your next row from the CURRENT record of the recordset.
'When you have finished splitting the current record into multiple rows:
.MoveNext 'Move to next record in recordset.
Loop 'Go Back to Do While to check if we are at the end of the file.
End With
'Close the Text file that now has each of your records split into multiple lines of Text.
Close #1
Exit_YourFunctionName:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function
Err_YourFunctionName:
Resume Exit_YourFunctionName
End Function
When you're done with this, you will want to import the Text file into a Table in your database.
Import | Delimited.
If you are wanting to do this on a regular basis & want to automate it, you can put these steps into a Macro.
If you name the Macro 'AutoExec', you can have your Windows Task Scheduler open your database at a set time.
When the databse opens, AutoExec fires off and performs whatever you have in there.
Let me know if you have more questions.