Access behaviour with large files

[dtutton]

I have previously used good old dBaseIII as a manipulator for large data files with clipper routines.

I have a geological model with 3 million records and about 120MB in ASCII format(this is a small one the last was over a GB and 8 million records). For ease of handling Ive broken down into 600k record files which contains 7 fields including x,y,z coords.

I imported one file with no problem into a table and managed to run a queries on it (this had 50 thousand records but 42 fields).

The second file imported OK into the same database but the third bombed (same format as second).

On trying to run a simple query (Max value of y) on the second table, I get an error "Invalid Argument" when I try to save the query. Also same when just listing values in the query.

Somehow I think Ive hit the limit of capacity with a database in Access (this Im doing in Access2k).

Has anyone had experience of access behaviour with large files.

Is it back to dBase and DOS?

David

 

[ghubbell]

Good Morning David!
Please check File-Database properties and notice the size. Try a compact repair too. Check the size again. Do you have lots of indexes on the tables? If you do and they're not too necessary, remove them, compact repair once more. Still no go? Maybe there's a typo in your query or data in the query that doesn't possibly agree with criteria? Null or empty records? Gord
ghubbell@total.net

 

[dtutton]

Gord,

There were no indexes, sorts or keys and query syntax ok.

The mdb size had balooned to just over 2gB and I assume this is the cause. After compacting, reduces to 54.6MB ! The underling ascii data imported was only 27MB at this point. Query runs fine now and does not affect database size.

Now the fun. Tried importing the second (20MB) of five files into an empty table in same database. Crashed on import (txt file) with "Invalid Argument" at end of import. The new table is empty and the database has expanded to ....yes 2GB.

Problem appears to be on import to existing (empty) table from txt format.

Any thoughts - maybe Ill import to dBase and then try importing from dBase but somehow defeats the object...


Thanks (and thanks for help on the semi-autonumbering issue - runs fine except with memo fields, (Im still not sure why the defaultvalue method worked and the variables didnt)

David

 

[ghubbell]

Your welcome David. Try to import in to a fresh empty database and see if it does the same thing. Once again...might be those Gremlins! Gord
ghubbell@total.net

 

[dtutton]

Gord,

I tried importing again and have same problem - It seems to explode with a text import of large files and crashes at 2GB when only 100MB .

What I have done now is the model T approach. I imported all the ascii files into a single dBase III table in dBase and have a 3 million record dbf file of 234MB. I then did a import link into access and the query works fine. The best news is the Access file is only 118kB. The Query is slow (probably not slower than if it was an access table) but acceptable and offers more than dBase could in dos.

Seems like Ill keep Windows98, Dos and dBase after all !

Thanks,

David

 

[DISI]

David,

Have you found the answer to this problem yet? I routinely have similar problems as I pull text files into Access monthly. I would love to hear from you if you have any unpublished help.

This makes no sense, but I have found that last few times that if I change the text file properties so it is NOT "read only" I can append without the excessive file bloat. My files are read only because I burn then onto a CD to transfer them onto my PC.

I would love to hear from you.

Paul Paul Faculjak
paul@DataIntegritySolutions.com

 

[dtutton]

Paul,

At present I havent found a solution and have found that Im not alone with problem. Ill give your idea a try and let you know. At the moment Ive reverted to the dBaseIII solution.

Ill keep you posted.

David Tutton
dtmineconsulting.com