Export table to several text files

[ShadeTree]

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
FROM test
WHERE [test].[Style]="1E";

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:



Sub mySub()
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"
rstStyle.MoveNext
Loop

rstStyle.Close
Set rstStyle = Nothing

End Sub


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.

[June7]

Try, note the space in front of FROM and concatenation of the variable:

& " FROM test WHERE test.style = '" & rstStyle!style & "'"

If style is numeric field, remove the apostrophe delimters.

[ssanfu]

In addition to what June7 said, I saw a couple of other problems. It looks like you want the name of the text file to be the style. Try the following:

Option Compare Database
Option Explicit

Sub mySub() 

  Dim rstStyle As DAO.Recordset
  Dim sSQL As String

  Set rstStyle = CurrentDb.OpenRecordset("SELECT DISTINCT [style] FROM test")

  If Not (rstStyle.BOF And rstStyle.EOF) Then
    rstStyle.MoveFirst
    'Process until end of file
    Do While Not rstStyle.EOF

      sSQL = "SELECT test.*"
      sSQL = sSQL & " INTO [text;DATABASE=C:\sample\]." & rstStyle!Style & ".txt"
      sSQL = sSQL & " FROM test where test.style = '" & rstStyle!Style & "'"

      'uncomment the following line and set a breakpoint to see the SQL
      '      Debug.Print sSQL

      CurrentDb.Execute sSQL, dbFailOnError

      rstStyle.MoveNext
    Loop
  End If

  rstStyle.Close
  Set rstStyle = Nothing

End Sub

[ShadeTree]

Snafu, June 7;

Many thanks. Your suggestions worked like a champ.

The code below is the finished & working sub.

Sub mySub()
Dim rstStyle As DAO.Recordset
Dim sSQL As String

'Each time I need this procedure, the values in [style] could be different
'So I need to create a list of the valid [style] values
Set rstStyle = CurrentDb.OpenRecordset("SELECT DISTINCT [style] FROM test")

If Not (rstStyle.BOF And rstStyle.EOF) Then

'Process until end of file
Do While Not rstStyle.EOF

sSQL = "Select [test].* "
sSQL = sSQL & "INTO [text;DATABASE=C:\sample\]." & rstStyle!Style & ".txt "
sSQL = sSQL & "from test where test.style = '" & rstStyle!Style & "'"

'uncomment the following line and set a breakpoint to see the SQL
'Debug.Print sSQL

CurrentDb.Execute sSQL, dbFailOnError

rstStyle.MoveNext
Loop
End If

rstStyle.Close
Set rstStyle = Nothing

End Sub