MS Access limitation

[manny18]

just want to know if the limitation of MSAccess of 2 GB is for one database only or for all the MSAccess database combined?

[dglienna]

Found it:

1) No row limit, total .mdb size is limited by 2Gb
2) Max 255 columns
3) Use ODBC and e.g. MySQL

In general the 2Gb limit will be large enough, but when needed you can place tables in different .mdb's and link them to a "master" .mdb, making the effective size many times bigger.

Here, scroll down to the answers (http://www.experts-exchange.com/Databases/MS_Access/Q_21024260.html)

[RobDog888]

The 2 Gb limit is per database for Access 2000 and newer. 1 Gb for Access 97.

[manny18]

ok thanks a lot.

[gigemboy]

I have never seen an access database go over 100 MB, personally... usually if you have that much data, they are using some other database program....... I don't even see how you could go over 2GB with text. It's not good to store images in a database, or video, or really anything other than text. Best to store pointers to the actual files, and store those types of files in a directory outside of the database...

[RobDog888]

True but i have seena nd used DBs that were 600-800 Mbs in size with no issues. All text and no blob field data either. It was involving an analysis/logging application where it had a few tables of 1,000,000+ records.

[uk_codemonkey]

I have never seen an access database go over 100 MB, personally... usually if you have that much data, they are using some other database program....... I don't even see how you could go over 2GB with text. It's not good to store images in a database, or video, or really anything other than text. Best to store pointers to the actual files, and store those types of files in a directory outside of the database...

I used to maintain an Access database that was about 900Mb in size. Thankfully we went to SQL Server :)

[-TPM-]

'Compact & Repair' is your friend ;)

[RobDog888]

Yes it is but with large dbs it takes forever to C&R.

[-TPM-]

I believe it's like defrag, the more you run it the quicker it is.

[RobDog888]

Its more like a defrag and clean up. It clears out "temp" table space used for various operations and stuff. When the db is closed it doesnt get deleted or released. So the C&R is necessary to keep it running it tip top shape.

[-TPM-]

Its more like a defrag and clean up. It clears out "temp" table space used for various operations and stuff. When the db is closed it doesnt get deleted or released. So the C&R is necessary to keep it running it tip top shape.

When I said it's like defrag I ment more that if you run it once a week it'll finish in less time than if you run it once a year...

[RobDog888]

But thats subjective. What if one day you dont add any records and another day you add 1,000,000 records . If you run C&R it will still take a long time because you added allot of records/objects one time and not the other.

[-TPM-]

True, the same could also be said for Defrag. How often does that happen in real life though...