April 23rd, 2012, 05:41 PM
MS Access Export table to several text files
I am pretty new to Access programming. I need to export a single table into multiple tables based on the value in a single field.
To restate... I have a table with 100,000 plus records that contains a field [style]. There can be as many as 300 unique values for the field [style]. I have been using the following query to export the file into text files
SELECT test.* INTO [text;DATABASE=C:\sample\].1E.txt
It works fine, but I must edit the query for each value in the [style] field. It is a little brutal & obviously lends itself to errors.
I have cobbled together the following code in an attempt to automate this task:
Dim rstStyle As DAO.Recordset
Set rstStyle = CurrentDb.OpenRecordset("SELECT DISTINCT [style] FROM test")
'Process until end of file
Do While rstStyle.EOF = False
CurrentDb.Execute "Select test.* INTO [text;DATABASE=C:\sample\].rstStyle!style.txt"" _
& "from test where test.style = rstStyle!style"
Set rstStyle = Nothing
I am getting an error stating "Query input must contain at least one table or query" & the query after CurrentDb.Execute is highlighted. Since I essentially copied the working query into this code, I don't know what the problem could be.
Suggestions would be appreciated.
April 23rd, 2012, 06:47 PM
Assuming the query is right this will get your query results into the query as you intended.
CurrentDb.Execute "Select test.* INTO [text;DATABASE=C:\sample\]." & rstStyle!style & ".txt" & _
" from test where test.style = '" & rstStyle!style & "'"
“Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
April 24th, 2012, 10:34 AM
That healed it.