Error when executing Select Into Statement

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?

Answer 1

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

Answer 2

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

Answer 3

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?

Answer 4

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