Reducing a Massive Linked Table

[raywood1]

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?

 

[Andrzejek]

I would just transfer (copy) the data from 'linked a 1.9GB text file (28 million records, 3 fields)' into a simple, 3 fields Access table.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[raywood1]

Thanks for the quick reply. Unfortunately, the resulting table exceeds 2GB. Access won't complete the operation. Unless maybe there's a copying method I'm not familiar with.

 

[Andrzejek]

I would be tempted to do the copying 'by-hand' (so to speak...)
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

 

[dhookom]

Since you "need only a subset of those records", I would use an editor to split the file into to files. Then import the data into two separate Access files (or link) and filter for the needed records.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016