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?

Revisory

SQLServer Express 2008 has a 10GB limit. Drop down access and switch to a better database. – Grind

If i use SQLServer Express 2008, What's the minimum requirements of client? – Revisory

I agree that SQL Server is a good idea once you are getting above the MS Access size limit. The main problem will be the additional training necessary to properly maintain an SQL Server back-end. – Beaty

Where is the size coming from? 2GB is large enough for most situations and SQL Server without an IT dept could be a problem. – Beaty

Requirements SQL Server Express 2008 microsoft.com/en-us/download/details.aspx?id=23650 – Exhaustless

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.

Incidental

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

Bobolink

Also, be sure to compact your database regularly to prevent buildup of unneccessary garbage. I know this doesn't answer your question on how to increase the size limit of an access database, but it will help prevent you from reaching that limit. If you want to set this up to compact, you could run this code on close of the database. vbadud.blogspot.com/2007/09/… – Burundi

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'

Pilgrimage

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

Patrimony