#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep 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
    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.
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Code:
    CurrentDb.Execute "Select test.* INTO [text;DATABASE=C:\sample\]." & rstStyle!style & ".txt" & _
     " from test where test.style = '" & rstStyle!style & "'"
    Assuming the query is right this will get your query results into the query as you intended.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    2
    Rep Power
    0
    Many thanks!

    That healed it.

IMN logo majestic logo threadwatch logo seochat tools logo