DB Size

[GMLWORLDCHAMP!]

You're right...sorry Pat!

 

[GMLWORLDCHAMP!]

File size limit...MET!

I have a database that I can now confirm has met the 2GB file size limit and wanted to share the errors and behaviors I've received so everyone will know what to expect...

From a C+/C++ original file that is ± 410MB; zipped:

The Access equivalent for this file is 1,996,875MB/1.99GB. Access will not recognize the file extraction from the zip file to Access and displays the following error message;

"Error reading the file."

Also, if you try to work around this issue by going directly to the zipped file folder and try to copy the full Access file, the Windows Explorer window will crash and close.

To test this further, I will try to re-download the original packed file (410MB file) just to make sure there were no download errors or issues and then try to extract the file into Access again but if I receive the same error message, I think the file size limit is what has happened so I'll have to have the original file split into multiple databases and just make my recommendations going forward with a new relational database.

I hope this helps anyone wondering about this issue!

 

[The_Doc_Man]

GML, I would make a backup copy of the DB, then try to compress it to see if you will be able to farther from here. Also "repair" might be helpful.

But of course you are right - time to split the DB. Or perhaps to at least archive some of the older entries.

 

[GMLWORLDCHAMP!]

GML, I would make a backup copy of the DB, then try to compress it to see if you will be able to farther from here. Also "repair" might be helpful.

But of course you are right - time to split the DB. Or perhaps to at least archive some of the older entries.

Right on...This was one of the first steps I tried this morning but being that the original 410MB file was already 99% compressed, it only made about a 1MB change in the size and still would not open so for the time being, I did create another mdb and only imported the tables I really needed to report on...bandaids to cover a BIG cut?$!?

Unfortunately, this system's data is not such that records can be archived because any record may be called upon at any time and added to. Hopefully, my principles will take my advice to take this system to the next level as I have just served as a Business Analyst and Project manager for my former company and now, current client, developing an IBM Universe relational database.

Another behavior I'd like to share with a file this large was that I noticed in the Task Manager the Performance percentage of the CPU jumped from top highs to bottom lows; huge percentage swings, instead of the normal behavior of jumping steadily and evening out around the same level...VERY WEIRD and it's not even Halloween yet!?#?$!

 

[GMLWORLDCHAMP!]

GML, I would make a backup copy of the DB, then try to compress it to see if you will be able to farther from here. Also "repair" might be helpful.

But of course you are right - time to split the DB. Or perhaps to at least archive some of the older entries.

Anyone ever run across this?

That same database I mentioned above now has further issues that I have been able to identify as a TABLE file size limit.

Having tried many workarounds; split database, various compacts/repairs on the full file, and decreasing the number of tables, there is a table that features customer responses to a questionnaire.

Several nested queries pulling data from this table worked perfectly just yesterday when the record count was 7,960,618.

With an updated database file three days later, this table now features 8,050,000 records and the same nested queries that worked before, now, do not.

The behavior of the query design window was strange in that, as usual, when a query is running, the progress bar at the bottom of the window appears and runs until the query results are displayed. But in this case, trying to dram against that 8million+ table, the progess bar does not appear and after 30 seconds or so, the query finishes by showing all of it's respective headings but with no records at all.

Again, going back to the former version of this database and an earlier count in this huge table, these nested queries ran just fine...

No workaround to lessen the overall database size worked. No split database model worked.

Time to get those new relational database developments cooking and QUICK!!!

I hope these descriptions help others explain what can happen with these file and table sizes!

 

[grnzbra]

If your problem is that you just have an incredible amount of data you can move the really big table to their own database and link to it. 3 Jobs ago, the database automatically downloaded a very large amount of info about every stock on the New York stock exchange. Each table was in its own database. By about mid november, we had to start keeping an eye on one of them because it was reaching the 2 Gig limit. (yes, we compacted it every night). Eventually we split the table in two and had each in its own database and linked to both of them.

 

[GMLWORLDCHAMP!]

If your problem is that you just have an incredible amount of data you can move the really big table to their own database and link to it. 3 Jobs ago, the database automatically downloaded a very large amount of info about every stock on the New York stock exchange. Each table was in its own database. By about mid november, we had to start keeping an eye on one of them because it was reaching the 2 Gig limit. (yes, we compacted it every night). Eventually we split the table in two and had each in its own database and linked to both of them.

This is our only option remaining because all of the values in the huge table must be maintained and with over 8 million, I crashed the Jet again by trying to lessen the record count significantly by identifying only the vendors we wanted...that's good because it creates a real framing of what exactly Access can and can't do.

A very interesting process because, like many, I've always wondered what the real limits of Access might be but never in a position with a project that really pushed it's documented limits so this is very good to learn!

I've got another Access project to help manage my home business wholeselling business and I'm going to test Access' ability as it relates to video and image capture!

 

[Compressor]

Soww..... having read all this.... what will happen with Vista and Access from Office 2007 to the maximum file size? Did anyone check already?

 

[freakazeud]

re:

The File Size limit will still be 2GB.
HTH
Good luck

 

[Compressor]

Ok. Thanks.

 

[freakazeud]

re:

No problemo.
Good luck on future projects!

 

[Dreamweaver]

I run a very large Db that has 5 Main Datafiles and another 7 Saturllite import system db's as I can send and recieve data from other users.

That main 5 db's when compacted are over 800Mb and the import db's range from 500Mb to 1.5Gb depending on how behind I am LOL

I had a major problem with speed as the db uses a lot of automated functions plus when Charts Are Added it has to check a table with well over 1.6Million Entiries for spcific things I solved my speed problem completey out of shear desperation I Bcked up the master (Then A Single DB) then replicated It the main db's size went up but I got my speed back so it was a very good trade, I have since split it into 5 (4 Replicted) It's been like that now for years without any problems (Touch Wood)

Check Out The pic For The Charts entry it is completly automated type a number in the last postion and it gets the data in less than a second from last weeks chart, Adding new records (Items Not in last weeks chart) is a lot more Complex as it also use a number of auto correct systems plus check if it's been in that chart before (Took A year to get it right)
Pic11