Access Database Homepage

Question: How to increase access database file size more than 2 giga bytes?

(Posted by: emperorx2000 on 2010-07-08 07:59:05)

I have built a database using microsoft access 2007, but my database file size reach a critical area (near 2 giga bytes) and access begin to deveolp problems, so how to overcome this size limit problem with my best regards

Posted by: Toby on 2010-07-08, 08:02:10

It sounds like you're project is too heavy for Access, which isn't meant to store that much data. Get MySQL, and if you also install the MySQL ODBC drivers, then you can hook into your MySQL database using Access as a front end if you want to.

Posted by: Colton on 2010-07-08, 08:00:42

Create a second Database with the name YOUR_DATABASE_NAME_2 and add all new data there.

Posted by: Fenris Wolf on 2010-07-08, 08:02:24

That's the end of it. No more. MS has artificially limited the size of any array, collection, etc. to 2^31 bytes. You're there. Complain to MS until they open it up for larger collections.

Posted by: Bob M on 2010-07-08, 08:59:27

Instead of using MS tables, you export them, then in Access use references to the tables instead of having them in the Access file itself. Try this with a new database - Create a database table, put in a few fields of various types. Then export it, I don't know what export formats are available to you these days but I always went for DB4, just don't choose MDA or any other MS format, then delete it off the Access tables list. Now create a link to that table in Access. Write a script to fill the table up.and you will find that you have beaten the MS file size limit. Not entirely, because Access will still be deathly slow if you query large ammounts of data, so if you can keep your queries smallish. Better still, put your data into an external dataengine, such as Postgresql or MySQL, you still reference these from Access, but now Access sends your SQL to these engines and lets them do all the work. Even on a local machine it works out better, but you are now free to have the sata on another machine (server or client) and your applications on the local client. You also now have the option now to directly access the data engine from other MS Applications or your own code, independant of Access. Access has it's place, nice neat local databases, but when you reach the big time data handling it just hasn't got what it takes to handle it.