First Published 2 Oct 2023
This article is an extended version of a response I made to several questions in a thread at Utter Access forum: Office 365 Personal
The questions were almost completely unrelated to the thread topic and were by an experienced developer (who I will call DN) with an unusual way of working in Access.
DN's approach to Access (lets call it his DNA!) includes:
a) very large numbers of database objects in an unsplit database
b) very extensive use of graphics (largely created in PowerPoint) and of text to speech
c) widespread use of multivalued fields (MVFs)
d) use of macros in preference to VBA code
It is worth stressing that DN achieves some visually very impressive results using his approach but it includes many features that to others have serious disadvantages.
DN is aware that the vast majority of developers do not agree with his approach but he has made clear that he not going to change the way his application works.
As a result he is continually pushing against several of the structural limits of Access.
In the thread at Utter Access, DN wrote:
Everyone tells me that the maximum size of an Access database is 2 GB.
However I have had more than 2Gb for at least a year, and I keep adding more tables, forms, and reports.
So, I wonder if the 2Gb mark is a hallucination. Does it really apply to 64-bit Access?
But my question goes beyond the size limit. I would like to know how many forms and reports I can have.
I have read the design number, but what is the practical number? Is it 10,000 or 20,000?
I am at 8,000 now. To access all the forms and reports in my application, I have 30 switchboards, each with as many as 136 command buttons.
And is there a limit to the number of MVFs that I can have in a table and ACCDB? I know I need more.
That's less important now that MS has made changes to the way relationship maps are created. But can I have more than 10 MVFs in a table? More than 20? Is there a limit?
The following is an expanded version of my reply:
I have repeatedly tested the 2 GB file size limit using both 32-bit & 64-bit Access. See the Microsoft article in link 1 below: Access Specifications
The following is one approach I have used for testing purposes:
I have a very large UK postcodes table of just over 1.4 GB and I created 2 links to that table in a new blank front-end (FE) database. The FE is about 0.4 MB at that point.
I then converted the first link to a local table - that works successfully and the file size increases to just over 1.4 GB.
However, the FE now runs more slowly due to its large size.
When I try to convert the second table to local, the process crashes when the file size is 2,095,552 KB.
Any further attempts to add data result in the application becoming corrupted and unusable
As there are 1024 KB in 1 MB, this is 2046.44 MB. Divide by 1024 again and that is equal to 1.99847 GB!
I have never been able to exceed 2 GB and maintain a useable Access database. That size limit applies to both 32-bit & 64-bit Access.
Splitting an Access database with the tables linked in a backend database (e.g. Access or SQL Server) and all other objects in the front end database has many advantages:
a) each database can be up to 2 GB thereby increasing the total size available
b) ease of maintenance - new versions of the front end can be developed and distributed to clients without affecting the data in the back end tables
c) improved performance - the front end will be smaller and respond far faster
The maximum number of objects in an Access database is 32768 (integer limit). That cannot be exceeded to the best of my knowledge.
There are specific limits on the number of open tables. This was recently doubled from 2048 to 4096 - see link 2 below: Now you can open more databases and tables
This number sounds like a lot but bear in mind this total includes:
a) temp tables used by Access to manage forms & reports e.g. listbox & combo box row sources
b) temp tables used whenever you sort or filter data
c) the deep hidden attached tables created to handle each single complex datatype type (multivalued / attachment or column history) field that you create.
So if you have 500 tables each with 30 MVF fields, that is 15,000 deep hidden attached system tables.
Each form that uses 1 table containing 30 MVFs would actually require 31 open tables. Each of those uses 4 or 6 connections - a total of well over 100 connections just for one open form.
This would drastically reduce the number of available connections and significantly impact performance.
NOTE: The total number of available connections was also recently doubled from 256 to 512 but once again, performance deteriorates as the number of available connections approaches that limit.
See link 3: Available Connections
As far as I am aware, there is no specific hard limit for the number of forms within the overall limit of objects.
However, as far as end users are concerned, the practical limit in terms of forms is far LESS than the 8000 forms you already have.
There is a limit on the number of forms, reports & modules which have code.
Officially that limit is 1000 code modules but in practice, its over 5000. See link 4: Access Specifications Issues
Even if you just use code for nothing other than text-to-speech on 5000+ of your forms, you will hit that limit.
Once again, performance deteriorates markedly as the number approaches anything like that limit.
There are also limits for the total number of controls over the lifetime of a form or report.
Officially that limit is 754, but in recent versions it is actually slightly over 1000. Once again, see link 4 Access Specifications Issues
All the numbers quoted in that article are based on detailed tests I have done in the past few months
Further Reading
1. Access Specifications
2. Now you can open more databases and tables
3. Available Connections
4. Access Specifications Issues
Feedback
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 2 Oct 2023
|
Return to Access Blog Page
|
Return to Top
|