How to increase MS Access 2007 database size?

Question

I developed a windows application, back end DB is Access 2007. I heard that max limit of Access 2007 is 2GB.

Now my question is, is there any way to increase the size beyond that limit? How to create more than one db for a application to increase size and performance?

Answer 1

You can partition your data into one or more additional database files, then create links to the satellite tables from your main application database.

Although that strategy could allow you to use more than 2 GB of data stored in Access, it is not a great choice. One pitfall is that you can't enforce referential integrity between tables in different db files; that fact alone could make partitioning a non-starter for many applications.

Another issue is temporary work space. When the db engine needs a disk file for work space, it uses a temporary file. And that temporary file is also restricted to a max of 2 GB. So if you have 6 GB of data spread between 4 db files of 1.5 GB each, and you need to do something which requires working with more than 2 GB of that data, you could get an error message complaining about lack of disk space. It doesn't mean the disk is full; it means the required temporary work space is greater than 2 GB.

Having actually done that partitioning once in the past, I don't want to ever do it again. Asking Access to manage that much data is just unreasonable; I had to spend way too much time waiting on it to do nearly anything I asked. It's much better to off-load that work to a more capable database system. So it's not only an issue of total storage capacity, but also how well the storage database can cope with large data sets. Your Access application could become a client to a client-server database.

I also think you should critically examine what you're storing. For example, some folks like to store images. I don't. Instead I store the path to an image file. Same for other file types. Perhaps you might find you can live without storing BLOBs, you can reduce your db size comfortably below the 2 GB limit, and continue with Access as storage.

Answer 2

The maximum database size is 2GB minus space required for system objects, but you can try one workaround: by splitting database. For more informations see:

Office.com - Split an Access database

MS KB304932 - How to manually split Access database

Answer 3

I had the same problem when my DB reaches 2GB upon importing my external data in the tables. I just disable Cache on the settings

Go to File > Options > Current Database

Under Caching Web Service and Sharepoint Tables

Check 'Use the cache format that is compatible with Microsoft Access 2010 and later'

Check 'Clear Cache on Close'

Check 'Never Cache'

Answer 4

I had the same problem on a db that had a lot of input over 17 years and was closing on the 2GB limit.It was already split so nothing could be done there. In the end I found by fiddling with the biggest table that the problem was embedded JPG images of which there was a very large number - It was popular for example to use an embedded image of employees or workers in tables in access. The solution which I am now implementing is going well. All the picures are in a file anyway so devise a new form that uses hyperlink to link to the pictures with a little code. delete the embedded images in the backend.I have already simulated this on a DB copy. It reduces size from 1.6 GB to less than 300 MB- so befor resorting to SQL you could look at that as a solution