How to increase MS Access 2007 database size?
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
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.