How to increase the size of the MS Access 2007 database?

Question

I developed an application for Windows, back end DB - Access 2007. I heard that the maximum limit of Access 2007 is 2 GB.

Now my question is: is there a way to increase the size outside this limit? How to create more than one db for an application to increase size and performance?

Answer 1

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

Although this strategy may allow you to use more than 2 GB of data stored in Access, it is not a great choice. One mistake is that you cannot provide referential integrity between tables in different db files; this fact in itself can make a partition into a non-stationary system for many applications.

Another problem is temporary workspace. When the db engine needs a disk file for workspace, it uses a temporary file. And this temporary file is also limited to a maximum of 2 GB. Therefore, if you have 6 GB of data distributed between 4 dB files of 1.5 GB each, and you need to do something that requires working with more than 2 GB of this data, you may receive an error message complaining lack of disk space. This does not mean that the disk is full; this means that the required temporary workspace is more than 2 GB.

In fact, having made this separation once in the past, I no longer want to do this. To request access to manage this large amount of data is simply unreasonable; I had to spend too much time waiting for him to do almost everything I asked. It is much better to disable this work for a more efficient database system. Thus, this is not only a problem of the overall storage capacity, but also how well the storage database can handle large data sets. Access can become a client of a client-server database.

I also think that you should critically examine what you store. For example, some people like to store images. I do not. Instead, I save the path to the image file. The same goes for other file types. You may find that you can live without storing BLOB files, you can reduce the size of your DB-drive to a level below 2 GB and continue access to the storage.

Answer 2

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

Office.com - Split Access Database

MS KB304932 - How to Manually Split an Access Database

Answer 3

I had the same problem when my DB reached 2 GB when importing my external data into tables. I just turned off the cache in the settings

Go to File> Options> Current Database

In the section "Caching Web Services and Sharepoint Tables"

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

Check "Clear cache on close"

Check "Never Cache"

Answer 4

I had the same problem on db, which had a big contribution for 17 years and closed at a 2GB limit. It was already divided, so there was nothing there. In the end, I found, playing with the largest table, that the problem was embedded in JPG images, of which there were a very large number. It was popular, for example, to use an embedded image of employees or workers in spreadsheets. The solution that I am implementing now is going well. In any case, all pickers are developing a new form that uses a hyperlink to link to photos with a small code. remove inline images in backend. I have already modeled this on a copy of the database. It reduces the size from 1.6 GB to less than 300 MB, therefore, to resort to SQL, you could consider this as a solution