The discussion today will explain about Access Database Size Limit. Despite the reliability, Access do has limitation for saving the tables and data.
An Access database in both .mdb and .accdb extension has maximum file size in 2 GB. Exceeding the file size limit will cause errors. The Access file will be corrupted, even using Compact and Repair feature probably will not repair the database.
Aside of the limitation in the file size, you can see the list of other limitations:
| Total number of objects in a database | 32,768 |
| Number of modules | 1,000 |
| Number of concurrent users | 255 |
| Number of open tables | 2,048 |
| Table size | 2 gigabyte minus other objects |
| Number of indexes in a table | 32 including indexes created internally to maintain table relationships, single-field and composite indexes. |
| Number of enforced relationships | 32 per table, minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships |
| Recordset size | 1 gigabyte |
| Number of characters in an SQL statement | Approximately 64,000 |
| Number of printed pages in a report | 65,536 |
| Number of controls and sections that you can add over the lifetime of the form or report | 754 |
| Number of characters in an SQL statement that serves as the Recordsource or Rowsource property of a form, report, or control. | 32,750 |
| Number of actions in a macro | 999 |
There is no way to overcome the limitation. However, do not too worry because 2 GB of database is totally huge and could stores a million records or thousands of database objects.
However, if you still thinking that 2GB is still is not enough, you can do one of these tricks:
- Separate the database. You can separate it periodically or categorize the database based on the organization functions. Advantage:
- More organized and easy to search.
- Faster to back up and maintenance because take more little time to load the data
- Easier to share
You can separate the database by splitting the database or create a database template.
- Link the Access database to server or other program. With this method, the data is stored in server, while the Access will only simply connect and display the database object and the data records.