Reducing a Massive Linked Table
Reducing a Massive Linked Table
(OP)
In Access 2021 on Windows 10, I have linked a 1.9GB text file (28 million records, 3 fields). I need only a subset of those records. Unfortunately, attempts to query the table to produce that subset result in an error:
The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.
There may be other solutions. I welcome suggestions of that nature. But here is my actual question:
Access sees two of that table's three fields as 255-character Short Text. In fact, I need only 75 characters to accommodate the entries in one of those two fields, and 36 characters for the other.
I don't know whether truncating those fields to those lengths in an output table or query would save enough space to avoid the error message. But is there a way to try?
The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.
There may be other solutions. I welcome suggestions of that nature. But here is my actual question:
Access sees two of that table's three fields as 255-character Short Text. In fact, I need only 75 characters to accommodate the entries in one of those two fields, and 36 characters for the other.
I don't know whether truncating those fields to those lengths in an output table or query would save enough space to avoid the error message. But is there a way to try?
Talk To Other Members
RE: Reducing a Massive Linked Table
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Reducing a Massive Linked Table
RE: Reducing a Massive Linked Table
Create a table in Access with 3 fields appropriately defined.
In code, read the text file record-by-record and insert every record into your table. It may take a while with 28 million records. You may also try to read 100 records and Insert the 100 records in one Insert statement. That may be faster, who knows...?
You will eventually find out if you can fit all the data into one table, or you may need to split the data into 2 or multiple tables, since there is a chance you may hit the limits of what Access can handle.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Reducing a Massive Linked Table
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016