Answered by:
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?
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.netMonday, 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