none
Error when executing Select Into Statement RRS feed

  • Question

  • I have a CSV file that I import into access into a table that has about 1.19 million records. From this table, I want to run a SELECT INTO query that concatenates 3 fields and filters out some data

    Here is the query:

    SELECT tbl_Temp.a & "_" & tbl_Temp.b & "_" & tbl_temp.c as Name, tbl_temp.* INTO tbl_working

    FROM tbl_Temp

    WHERE tbl_Temp.d="something"

    The query starts to run and then the following error message appears:

    "Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt"

    Running the query:

    SELECT tbl_Temp.a & "_" & tbl_Temp.b & "_" & tbl_temp.c as Name, tbl_temp.*

    FROM tbl_Temp

    WHERE tbl_Temp.d="something"

    results in 1.16 million records

    Is it a size thing?

    Monday, January 14, 2019 12:02 AM

Answers

  • If I use a linked table for the csv import, it works. I'm guessing its the overall size of the access database with two 1 million row+ tables
    • Marked as answer by JHarding08 Monday, January 14, 2019 6:08 PM
    Monday, January 14, 2019 6:08 PM

All replies

  • Have you tested on a reduced CSV file?
    You could try running your query over batches of record instead of the whole table.

    What is the size of your database?  During the process?


    Daniel Pineault, 2010-2018 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, January 14, 2019 12:10 AM
  • With the CSV imported into one table, the size of the accdb file, compacted and repaired is 1.3 GB. the CSV by itself is 622 MB.  the csv import into table works fine, all 1.9 million records come over, but the select into a new table is where the error occurs

    Monday, January 14, 2019 3:40 PM
  • If the db file is already 1.3GB, just a guess but maybe it's running out of room trying to process the query to create a new table from the CSV file. Can you try your query on a blank database just to make sure it's not the file size?
    Monday, January 14, 2019 4:40 PM
  • If I use a linked table for the csv import, it works. I'm guessing its the overall size of the access database with two 1 million row+ tables
    • Marked as answer by JHarding08 Monday, January 14, 2019 6:08 PM
    Monday, January 14, 2019 6:08 PM