There have to be limits to the number and size of many things about MS Access. The limits are fairly generous, but, of course, there are always situations that exceed any limit you care to pick! Below is a table of some of the limits you might run into.
|Total size of database||2 Gigabytes
including space needed for system objects that the database requires
|Number of objects||32,768
Total of all tables, queries, forms, reports, indices, etc. It's hard to count up the number of indices!
|Characters in name of table or field||64|
|Fields in a table||255|
including linked tables and internal tables opened by Microsoft Access itself
|Characters in a text field||255|
|Characters in a memo field||65,535 when entered through user interface.
1 gigabyte if entered programmatically
|Size of OLE object||1 gigabyte|
|Tables in a query||32
less if there are multivalued lookup fields included
|Fields in a recordset||255|
|Sort limit||255 characters in 1 or more fields|
|Levels of nested queries||50
less if there are multivalued lookup fields
|Characters in a label in form or report||2048|
|Characters in text box||65535|
|Form or report width||22.75"|
|Section height in form or report||22.75"|
|Number of levels of nested forms or reports||7|
|Number of printed pages in report||65535|
There are more limits than these! Look in the Help files for MS Access under Getting Started > Microsoft Access Specifications.
Your database may suffer from serious sluggishness long before it reaches the maximum size. The larger the database, the more important good design and good indexing become. Otherwise, the simplest action can take a long time to perform.
While there is no limit to the number of records in a table, the whole database has to fit inside 2 Gigabytes of space. How many records you can squeeze in will depend on how much data goes with each record.
When your database gets close to the maximum for any of the limits, it's time to think about switching to a more advanced database program.
Last updated: March 27, 2017