Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Reducing a Massive Linked Table

Reducing a Massive Linked Table

Reducing a Massive Linked Table

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?

RE: Reducing a Massive Linked Table

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

RE: Reducing a Massive Linked Table

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.

RE: Reducing a Massive Linked Table

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

RE: Reducing a Massive Linked Table

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.

Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close