Linked table size - text file

[JCRMet]

I have a delimited text file that is approximately 24 million records and occcupies just over 6 gig of space. I'm working on Access 97, so I knew I could not import the file. I attempted to link the file. I created a spec, went through the file import process, and got a table with the proper icon for linked text. However, when I open the table, it's empty. No records. I thought perhaps I could not look but could query, so I ran a simple query - same result - no errors but an empty response.
I truncated the file down to 13k records and both linked & imported no problem. I then tried to import only one field from the big file - reduced it from 288 across to 10 - same result again - no errors, I got an icon, but emptiness. Is this a file size limitation in Access?

 

[mpastore]

I'd wager it is the size. In the past I have taken 2 routes to handle files of this size:

1) Partition the file into a series of smaller files based on something like date. i.e. FY96, FY97, FY98, etc. The shortcoming here is that your queries will have to account for different files.

2) Import the file into a more robust DBMS like SQL server or Oracle then link the tables via ODBC.

Good luck!

Mike Pastore

Hats off to (Roy) Harper

 

[PHV]

I'm working on Access 97
You can't work with any table bigger than 1 GigaByte.
24 million records
Don't play with ANY version of access with such amount of data.

 

[BattleDroid42]

You're correct in that Access cannot handle importing a file of that size. The file size limit for an Access 97 database is 1GB. I would imagine that Access is having trouble sustaining a link of that magnitude due to a ODBC driver limitation.

The work around for this is if you must stay with Access is to clip the file into smaller pieces and create a database for each piece.

Personally, since my backend is in Oracle, I would import the data into Oracle using SQL*Loader which handles text files very well.

Remember, wherever you go...there you are.

 

[JCRMet]

I would love to be working with Oracle but am limited to Access for the moment. I understood the 1 gig limitation, but I did not think it was applied to linked tables. I was hoping that if I used the linking, I could work around the size limit. In regard to parsing the files, it was a download off of a mainframe, because of the size, I'd have to go up to the mainframe and parse it up there in TSO. Not something I've had a lot of experience with. The way I shortened the file to 13k records was to start a download and abort it.
Thanks for the suggestions.

 

[willir]

JCRMet

In order for a database application to work with "data", it needs an engine to do the work.

If for example, you used ODBC to access an Oracle database, the Oracle engine would run the backend database stuff and interface to the front end Access via ODBC.

delimited text file that is approximately 24 million records and occcupies just over 6 gig of space

With a delimited text file, what will provide the database engine magic to handle the backend? I suspect it would have to be the ODBC drive and Access.

You can upgrade to Access 2000 or XP, but even here, you will have a 2 GB size limit.
Size limitations

To verify this, can you work with a much smaller text file, meaning less records.

I truncated the file down to 13k records and both linked & imported no problem.

...but you have already done this.

I strongly agree with PHV in that you should be working with an alternate application such as MS*SQL or Oracle.

Richard