Visual Basic Programming: Microsoft Access Database Limits
The general limitations of Access 2003, Access 2007, Access 2010 and Access 2013 are unchanged.
Note that the following are theoretical limits, not practical limits. For example, the 255 maximum number of users bears no relationship to reality.
Microsoft Access is unlikely to be able to support more than 30 concurrent users on a highly optimised Application. A more acceptable limit is about 10 users.
Microsoft Access Database Limits
OLE Object field
Number of records
Limited by File size
Number of Tables
When the Microsoft Access file size limitation is reached
The first constraint that is likely to cause a problem to a growing administration Application is the Access database size limit of 2 gigabytes.
Before attempting a conversion to SQL Server (size limited to 524,272 terabytes!), here are some ways to reduce the Access database size:
- The database should be split between a Back-End database (containing the data) and a Front-End database (i.e. the Forms, Queries and Visual Basic logic).
- Split off all historic data. The historic Tables can be linked in the Front-End database, and provide a seamless view for the user.
- Remove all OLE Objects (i.e. Blobs: images, Word documents, etc), and replace them with references to a Windows folder path.
- Use Unicode compression on all Text fields.
- Remove all spurious, obsolete and unused fields.
- The Database should be normalised. This is the process of organising the columns of a table to reduce data redundancy and improve data integrity.
The drawbacks to splitting a Database:
There are several reasons not to split the Database too far. They are:
- The more a database is split, the harder it will be to convert to an SQL Server database. All foreign Keys and relationships that were removed will have to be reincorporated.
- Referential integrity cannot be enforced by Access between tables.
- Access cannot do cascading Updates or Deletes.
- Access uses a temporary file for work space. The temporary file is still restricted to a maximum of 2 GB for all the split databases.
- Access cannot optimise I/O paths – Query optimisation cannot be performed without foreign keys.