fbpx
Logo
Home » Access Free Stuff » Access Specifications: The Facts

Access Specifications: The Facts

accessdatabasetutorial

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 (there are some variations For newer versions).

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 database32,768

 

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

 

Number of characters in an object name64

 

Number of characters in a password14 for .MDB and 20 for .ACCDB

 

Number of characters in a user name or group name 20

 

Number of concurrent users255

 

Table


Attribute

Maximum / Comments
Number of characters in a table name64

 

Number of characters in a field name64

 

Number of fields in a table255

 

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

 

Table size2 gigabyte minus the space needed for the system objects

 

Number of characters in a Text field
255
Number of characters in a Memo field65,535 when
entering data through the user interface;
2 gigabytes of character storage when entering data programmatically
Size of an OLE Object field1 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 relationships32 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 query32*
Number of joins in a query16*
Number of fields in a recordset255
Recordset size1 gigabyte
Sort limit255 characters in one or more fields
Number of levels of nested queries50*
Number of characters in a cell in the query design grid1,024
Number of characters for a parameter in a parameter query255
Number of AND operators in a WHERE or HAVING clause99*
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 label2,048
Number of characters in a text box65,535
Form or report width22 in.
(55.87 cm)
Section height22 in.
(55.87 cm)
Height of all sections plus section headers (in Design view)200 in. (508cm)
Number of levels of nested forms or reports7
Number of fields or expressions that you can sort or group on in a report10
Number of headers and footers in a report1 report header/footer;

1 page header/footer;

10 group headers/footers

Number of printed pages in a report35,536
Number of controls and sections that you can add over the lifetime of the form or report754
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 macro999

 

Number of characters in a condition255

 

Number of characters in a comment255

 

Number of characters in an action argument255

 

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 name64
Number of columns in a table250
(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.