First Published 15 Feb 2023                                 Last Updated 4 Jan 2024                                 Difficulty level : Easy


Section Links (this page):
        Introduction
        Database password length
        Number of code modules
        Number of controls in form/report
        Number of printed pages in report
        Conclusions
        Feedback


1.   Introduction                                                                                                                                                                 Return To Top

Microsoft has published an article with a list of Access specifications and limits

Most of the information conmtained in the article is correct. However, there are several items that are misleading, out-of-date or inaccurate.

Unfortunately the article doesn't make clear which values are hard limits that cannot be exceeded and those items which are mainly intended as sensible guidelines.
Some hard limits may also have changed over the many versions of Access

The following are examples of hard limits which are correct

Database Object	Attribute	Maximum	Notes
General	Total size for an Access database (.accdb or .mdb), including all database objects and data	2 gigabytes, minus the space needed for system objects.
General	Total number of objects in a database	32,768
General	Number of characters in an object name	64
Table	Number of characters in a Short Text field	255
Table	Number of indexes in a table	32 including indexes created internally to maintain table relationships, single-field and composite indexes.
Query	Number of joins in a query	16
Form / Report	Form or report width	22.75 inches (57.79 cm)	Integer limit (32768) in TWIPS where 1440 twips = 1 inch
Form / Report	Section height	22.75 inches (57.79 cm)	As above

However, the following items are misleading, inaccurate or out of date:



2.   Database password length                                                                                                                                         Return To Top

Database Object	Attribute	Stated Maximum	Notes
General	Number of characters in a password	14 Note: For Access 2007, it is 20 characters.	The higher limit applies to all versions from A2007 onwards BUT there is an important caveat in some versions

For A2007 onwards, a database password can have up to 20 characters. More than 20 characters can be entered but any additional characters are not saved

However, there is a very important issue with linked tables. If a password of 20 characters is used, trying to connect a linked table fails with this error message:


This means that the maximum usable password limit is only 19 characters when used as a backend database.

For further details, see my article: Maximum Password Length in Access Files

The issue has been reported to Microsoft several times in recent years.

UPDATE - June 2023:
This issue was finally fixed in version 2305 (June 2023) for those with click-to-run versions of Access such as 365, 2021, 2019.
However, it has not been addressed in older versions that are no longer supported (2007/2010/2013)


Download:
Example frontend database with two backend databases encrypted with passwords:     Password Length Test     (approx 1.8 MB zipped)



3.   Number of code modules                                                                                                                                           Return To Top

Database Object	Attribute	Stated Maximum	Notes
General	Number of modules (including forms and reports that have the HasModule property set to True)	1,000	This is more of a guideline than a limit.

The value of 1000 quoted here is completely inaccurate. Even if it were ever true, the limit has been far higher than that for at least 15 years.

For example, one of my large commercial databases for schools has over 1300 code modules in total. There are about 550 forms and 650 reports with code modules plus about 130 standard/class modules.

As a quick test, I made a copy of all the forms & reports with code modules taking the total to well over 2500. The database performance was not noticeably affected.

Next I did a more thorough test, creating a blank database in 32-bit Access 365 and programmatically adding batches of standard and class modules each containing one 'dummy' procedure



At first, adding modules programmatically was fast but slowed significantly as more were added. I added a total of 5000 modules in batches of 500 over a period of time whenever the computer was idle.
I then attempted another batch of 500 modules overnight but returned the next morning to find that the process had crashed when there was a total of 5450 code modules.



After compacting the database, I tried adding individual form/report and standard code modules manually.
Every attempt failed, either causing Access to crash or displaying this error:



NOTE:
1.   It seems likely that the limit occurs when Access runs out of system resources.
      It may be that 64-bit Access would be able to accommodate additional code modules (not tested)
2.   Although it is possible to add well over 5000 code modules, that doesn't mean it is a good idea.
      Performance became increasingly sluggish well before the 'limit' was reached. Perhaps 1000 is a useful guideline for the maximum number of code modules in a database


Download:

Databases used in the above test:
a)   Database with 5450 code modules     Test Create Modules 5450     (approx 18 MB zipped)

b)   'Blank' database with code to add modules programmatically     Test Create Modules BLANK     (approx 0.4 MB zipped)

UPDATED 4 Jan 2024
Removed unused test code that prevented both of the above example apps compiling. Thanks to Xevi Batlle for informing me of the issue.



4.   Number of controls/sections in a form or report                                                                                                   Return To Top

Database Object	Attribute	Stated Maximum	Notes
Form / Report	Number of controls and sections that you can add over the lifetime of the form or report	754	This has long been out of date and is more of a guideline than a limit.

I have seen reports of the actual limit being well over 900 as far back as Access 2003. It has increased further since that time

I tested this limit by repeatedly cloning the form controls from my Better Date Picker example app

Using 32-bit Access 365, I reached a total of 1038 controls successfully



Attempting to add an additional control caused this error message



I removed a control and attempted to add another. It caused the same error.
This confirmed the article comment about the limit being over the lifetime of the form

I then copied the form and was able to add one more control to the new form... making the total 1038 again.

I was also able to open the form successfully in 32-bit Access 2010 but again unable to add any more controls.

Next I re-tested using 64-bit Access 365. This time I was able to add 129 additional controls with the new limit being 1167



On re-testing the modified form with 1167 controls in 32-bit Access, the following error occurred:



Removing controls to bring the total back to 1038 triggered numerous errors but eventually the form opened successfully.

So the limit clearly does depend on available resources. As 64-bit Access can make better use of available resources, it has a higher limit

Very few forms/reports will ever need anything like this number of forms/controls if properly designed.
However there are some valid exceptions. For example, creating a form/report to show the seating plan for a concert hall.

If you really do need many controls and hit the limit, try making a new copy of the object so the lifetime limit re-starts from zero.
If any users have 32-bit Access, make sure you don't exceed the lower limit of 1038 controls

I also recommend splitting the controls into groups in one or more subforms/subreports.
This will make organising a large number of controls easier to manage and help avoid hitting the limit

In this rather extreme example, the 2 subforms have 1033 and 1038 controls respectively. However the main form has just 5 controls - the 2 subforms and 3 labels



NOTE:
Do bear in mind that performance may still be affected and you may hit other system resource limits


Download:

Database used in the above test:     Control Limit Test     (approx 6.3 MB zipped)



5.   Number of printed pages in a report                                                                                                                        Return To Top

Database Object	Attribute	Stated Maximum	Notes
Report	Number of printed pages in a report	65,536	This is the total number of values in the integer range

Although very few printed reports should ever have anything like this number of pages, it is certainly possible to exceed the quoted value many times over.

However the page numbering becomes problematic once the total number of pages exceeds the positive integer limit of 32,767
Above that value, the total pages shown revert to negative integer values starting with -32767 and increasing until at 65,536 pages, the total pages are displayed as 0
Increasing still further causes the total pages to increase again from zero and the process is repeated as many times as necessary

In a test, I created a report with a total over 2.6 million records displayed with 14 records per page. This gave a total of over 186,000 pages
For more details on this issue and how to show the correct number for the total pages, see my article Negative Total Pages on Report



6.   Conclusions                                                                                                                                                                  Return To Top

The specifications should in certain cases be treated as guidelines rather than hard limits

Where a quoted figure appears to have no logical explanation, it is probably only intended as a guide.
For example, I suspect the following limit is also inaccurate (untested)

Database Object	Attribute	Maximum	Notes
Macro	Number of actions in a macro	999	Not tested

If you are aware of other discrepancies, please let me know so I can add them to the article



7.   Feedback                                                                                                                                                                     Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                     Mendip Data Systems                     Last Updated 4 Jan 2024



Return to Access Articles Page Return to Top