April 23rd, 2012, 05:41 PM
Join Date: Apr 2012
Time spent in forums: 10 m 27 sec
Reputation Power: 0
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.