Neville
Silverman

Visual Basic Programmer, Sydney
Microsoft Access Database Programmer

Custom Built Software
System Optimisation
Office Automation

Microsoft Access Database Limits

Access 2003 through to Access 2013 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 system. A more acceptable limit is about 10 users.

Microsoft Access Database Limits

Object
Item
Limit
Access Database
File size
2 Gigabytes
 
Number of objects
32,768
 
Number of Modules
1,000
 
Number of users
255
Table
Table Name
64 characters
 
Field Name
64 characters
 
Number of fields
255
 
Text fields
255 characters
 
Memo field
65,535
 
OLE Object field
1 Gigabyte
 
Number of indexes
32
 
Number of records
Limited by File size
Queries
Number of Tables
32
 
SQL statement
64,000 characters
 
Number of ANDs
99

When the Microsoft Access file size limitation is reached

The first constraint that is likely to cause a problem to a growing administration system 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.