First Published 15 Feb 2023                                 Last Updated 23 Oct 2024                                 Difficulty level : Easy

UPDATED 23 Oct 2024: new information on the number of database objects and the number of actions in a macro.

Section Links:
        Introduction
        Number of Database Objects
        Database password length
        Number of code modules
        Number of controls in form/report
        Number of printed pages in report
        Number of actions in a macro
        Conclusions
        Feedback


1.   Introduction                                                                                                                                                                 Return To Top

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

Most of the information contained 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	See section 2 below
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.   Number of Database Objects                                                                                                                                     Return To Top

Database Object	Attribute	Stated Maximum	Notes
General	Total number of objects in a database	32768	32768 is more of a guideline than a limit. At least 75000 with no code modules but limit of about 5450 with code modules.

UPDATED 23 Oct 2024

When I first released this article in Feb 2023, I confidently stated that the specified number of database objects (32768) was a hard limit.
As the value supplied was equal to the integer limit it seemed correct, though it was the only item above I hadn't tested systematically.
Also, I had never heard of any developer who had exceeded that number of database objects - possibly because nobody had ever had a need to do so!

Recently, my Spanish Access developer colleague, Xevi Batlle, contacted me to say that this information was incorrect.
He had tested programmatically adding forms to a blank database, both with and without controls and code modules.

Xevi only hit a limit when the forms had code modules. The code errored when he hit 5450 forms with code modules.
This was identical to the limit I found in my earlier tests: See Number of Code Modules - section 4 below.

I set about running similar tests myself. The tests are easy to do but very time consuming and can use a large amount of system resources. It is recommended that any tests are left to run overnight or on a spare workstation as the creation or copying of objects dramatically slows down as the total number increases.

I decided to test whether the actual limit was the full integer range (65,536), but was able to exceed this total by a large amount reaching over 75,800 forms before stopping the tests.

In the end, neither Xevi nor I reached the absolute limit for the number of objects. Below is a summary of my tests on several separate databases:


The only one of the above tests that reached completion was adding modules. I decided to do one final test where I looped through copying each type of object in turn.

Once again, the routine crashed at around 5450 when attempting to copy a module (error 2003). These were the final results (including built-in system tables):


With the exception of modules, it may well be there is no actual limit.
In reality, it is likely that other limits will be hit first e.g. total number of code modules, total size of database (etc).

However in practical terms, a database with anything like this number of objects will be almost unusable. For example:
a)   the database may take several minutes to open.
b)   the navigation pane will become unresponsive making searching or sorting very slow.
c)   running queries / code will become increasingly slow and corruption may become more likely.
d)   compacting the database may take many minutes.

A well designed database should never need anything like the stated limit of 32768.

Just because you CAN exceed this number of objects doesn't mean you SHOULD do so.

In fact I would strongly recommend that no Access database should ever have more than a few thousand objects at most.

Download:
Example database used in the some of the above tests:
'Blank' database with code to add all object types programmatically     Test Copy All Objects BLANK     (approx 0.4 MB zipped)



3.   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 an important issue with linked tables in some versions. If a password of 20 characters is used, trying to connect a linked table fails with this error message:


For affected versions, 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)



4.   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.



5.   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)



6.   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



7.   Number of actions in a macro                                                                                                                        Return To Top

Database Object	Attribute	Stated Maximum	Notes
Macro	Number of actions in a macro	999	At least 2400 Treat as a guideline rather than a limit.

I very rarely use macros but find it difficult to understand why anyone would ever want anything like the stated maximum of 999 actions in a macro.
Nevertheless, this is another supposed limit that can be exceeded by a significant amount. I was able to create a macro with 2400 actions without issues.

To do so, I first created a macro with two actions: Beep and Message Box together with a comment.
I then exported the macro to a text file using the undocumented but very useful SaveAsText command.

I then cloned the two actions repeatedly in the text file to create new macros with 4 actions then 120, 1200 and finally 2400 actions.
Each of these was re-imported into Access using the (also) undocumented LoadFromText command.

Each macro ran without issues in Access. The only problem was that opening the macros in design view became slower as the number of objects increased.
There was nothing to suggest I was anywhere near a hard limit though the outcome may be different using a wider range of the available actions.

Perhaps once again this should be treated as a guideline to an upper limit. However in practice, I still cannot envisage a situation where anyone would need anything like this number of macro actions.

Download:
Example database used in the above test with various macros from 2 actions through to 2400:     Macro Actions Test DB     (approx 0.6 MB zipped)



8.   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.
There may be other supposed limits that are out of date, misleading or inaccurate that I haven't tested.

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



9.   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 23 Oct 2024



Return to Access Articles Page Return to Top