Access Specifications: The Facts

Microsoft Access database specifications

The following Access specification facts and figures are divided into object types and focuses on the Access database ‘JET’ engine (standard Access).

Unless stated, the following tables apply to all versions from 2003 through to 2010.

General


Attribute

Maximum / Comments
Access Database File Size

(.MDB and .ACCDB)

2 gigabytes, minus the space needed for system objects

NOTE:
Although the maximum size for a single database file is 2GB, you can work
around this limitation by using a split database. A front-end database file
can point to thousands of back-end database files, each of which could be as
large as 2GB. For more information, see the topic, Split a database.

 

Number of objects in a database 32,768

 

Number of
modules (including forms and reports that have the HasModule property set to True)
1,000

 

Number of characters in an object name 64

 

Number of characters in a password 14 for .MDB and 20 for .ACCDB

 

Number of characters in a user name or group name 20

 

Number of concurrent users 255

 

Table


Attribute

Maximum / Comments
Number of characters in a table name 64

 

Number of characters in a field name 64

 

Number of fields in a table 255

 

Number of open tables 2048; the actual number might be smaller because of tables opened internally by Access

 

Table size 2 gigabyte minus the space needed for the system objects

 

Number of characters in a Text field
255

 

Number of characters in a Memo field 65,535 when
entering data through the user interface;

2 gigabytes of character storage when entering data programmatically

Size of an OLE Object field 1 gigabyte

 

Number of indexes in a table
32
Number of fields in an index
10

 

Number of characters in a validation message
255

 

Number of characters in a validation rule
2,048

 

Number of characters in a table or field description
255

 

Number of characters in a record (excluding Memo and OLE Object fields) when the
UnicodeCompression
property of the fields is set to Yes

4,000

 

Number of characters in a field property setting
255

 

Query


Attribute

Maximum / Comments
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*
Number of tables in a query 32*
Number of joins in a query 16*
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50*
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of AND operators in a WHERE or HAVING clause 99*
Number of characters in an SQL statement
Approximately 64,000*


*Access specification – maximum values might be lower if the query includes multivalued (from version 2007) lookup fields.

Form and Report


Attribute

Maximum / Comments
Number of characters in a label 2,048
Number of characters in a text box 65,535
Form or report width 22 in.
(55.87 cm)
Section height 22 in.
(55.87 cm)
Height of all sections plus section headers (in Design view) 200 in. (508cm)
Number of levels of nested forms or reports 7
Number of fields or expressions that you can sort or group on in a report 10
Number of headers and footers in a report 1 report header/footer;

1 page header/footer;

10 group headers/footers

Number of printed pages in a report 35,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 (.mdb, .accdb and .adp) 32,750

Macro


Attribute

Maximum / Comments
Number of actions in a macro 999

 

Number of characters in a condition 255

 

Number of characters in a comment 255

 

Number of characters in an action argument 255

 

Access Project (.adp)


Attribute

Maximum / Comments
Number of objects in an Access project (.adp) 32,768
Number of modules (including forms and reports that have the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of columns in a table 250
(Microsoft SQL Server 6.5)

1024 (Microsoft SQL Server 7.0, 2000, 2005 and 2008)


Forms, Reports and Macros for .adp files have the same Access specification as .mdb/.accdb files.

The Access specification above should provide a useful resource especially when programming Microsoft Access using VBA.

The number of objects used especially for the heavier duty forms and reports can sometimes be a restriction and have had to use some work arounds to resolve the problem.

2 Replies to “Access Specifications: The Facts”

  1. I am following your instructions on how to distribute an Access DB and downloaded the EXE for Access 2007. I don’t follow what to do next?. Should I run this on my own machine or have the user run this prior to trying to use my DB?

    Regards

    Clive

    1. Hi,
      On your own machine, you should have the normal full open version of MS Access and do not need to run the EXE.
      For other users, if they don’t have a normal version or licensed copy of MS Access, they will first need to instal the EXE provided which installs just the runtime (not editing of DB will be allowed) and then will be open to your DB file in various formats namely accdb and accde.

Leave a Reply

Your email address will not be published. Required fields are marked *