Max # of Modules in Access 2013 Desktop Applicaiton

Posted on 2014-12-17
Medium Priority
Last Modified: 2014-12-24
The only documentation I can find states that an Access 2013 desktop application can contain no more than 1000 modules; that includes standard modules, class modules, forms with the property HasModule=true, and reports with the property HasModule=true. My application currently has 1097 such modules and still seems to be working fine. Does anyone the the correct maximum number of modules?
Question by:rmk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 3
  • +3

Author Comment

ID: 40505927
The last sentence had a type and should have been "Does anyone know the correct maximum number of modules?"
LVL 75
ID: 40505973
Not sure, but I think you just set the Guinness World Record for Modules in an Access Database!

And I hope you are backing up on a very regular basis ?


Author Comment

ID: 40505984
No real need to back up the front end as it exists on each work station and gets there as part of an automated version control system which copies the files from a server. The server versions are backed up regularly. This application has been around for 7 years and we are now on the 1059th version of the front end.
The back end is made up of 4 SQL Server databases and 1 DB2 database which are all part of a robust backup and recover system.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

LVL 75
ID: 40505997
OK ... but I was really referring to when you are doing development on the FE ... since VBA corruption is a fact of Access life, and Decompile may not fix it.  But sounds like you have it covered.

1000 seems like an arbitrary number to me anyway.

Author Comment

ID: 40506014
I have definitely been through the growing pains of not backing up and learned my lesson the hard way over the years. When a new version requires a lot of changes I sometimes backup 10 times a day and I always decompile and compact when I continue. I have several very large front ends for several clients and they are all very sensitive to corruption during the development process. It only took 2 instances of corruption to ingrain the backup discipline into my process. I've been working with Access since version 1.0 (back in 1992 / 1993 I think) and I'm finally going to (semi) retire at the end of this year. I think Access can do a lot more than a lot of people give it credit for, and I'm going to miss it (well maybe just a little bit).
LVL 26

Expert Comment

ID: 40506016
My application currently has 1097 such modules
I figured I was pretty heavy at 378 forms, reports and code modules with 111K lines of code.
You clearly have a super-heavyweight app on the go.

I've seen the A2007 and 2010 thousand module reference

1000 is an oddball number for a spec --> 1024, ok that I could understand.
I wish I could tell you how close you are to crashing your app.
I can't
LVL 75
ID: 40506031
Just be SURE you backup *before* you Decompile ... because Decompile - although very rare ... can FUBAR your db.

Author Comment

ID: 40506035
The app has about 150 users, with a peak of about 25 concurrent users. I really am amazed at how stable Access has been. Our most significant problems over the years have been 1) network connectivity between Houston and Dallas, 2) the lack of transaction support using ADO between Access and DB2, and of course 3) bugs in my application code. We've been caught a couple of times with problems caused by Windows or Office updates, but fortunately found a way around the problems in a reasonably short period of time. The only truly bizarre issue that is still with us today, is that I have to distribute the front end uncompiled; otherwise we experience random crashes on random machines. I gave up looking for the root cause a long time ago.
LVL 85
ID: 40506617
This application has been around for 7 years and we are now on the 1059th version of the front end.
I think maybe you also receive the award for Most Often Changed Application. According to my calculator, that's a "fix or upgrade" every 2.4 days over the course of 7 years, assuming a 7 day week. If you figure a 5 day week, that's a deployment every 1.6 days. During heavy development I'll regularly deploy a new update daily, but that generally only lasts for a few weeks, or at most a month or two. Keeping up that pace for 7 years is ... impressive (and a good way to stay in a job!).

I too have no idea of the number of Modules. Obviously it's more than MSFT is reporting. For me, I'd be very leery of a database that big, but as others have said, as long as you have regular backups and are careful about checking those backups, you could easily recover.

Author Comment

ID: 40506719
My client has a very create CFO who is always asking for a new feature, i.e. he often calls me and says "Can you make it do this?". From my perspective it's great because this is a time and materials client.

Author Comment

ID: 40506825
Scott your calculation for # of versions per day didn't seem right to me. So I checked my version control tables and found that the first release was more than 7 years ago on 04/20/2006. I actually started working on it before that but that's the date I implemented version tracking and version control.
LVL 75
ID: 40507531
Curious ... what is the Compacted & Repaired size of the Front End ?

Author Comment

ID: 40507582
If I decompile and compact and repair it is 102,644 KB.
If I compile and compact and repair it is 122,500 KB.
It currently has:
368 forms and subforms
236 reports and subreports
3,467 queries
517 linked tables
3 macros
469 modules (360 standard modules and 109 class modules)
354,821 lines of VBA code in modules, forms, and reports

Expert Comment

ID: 40508048
What have you found are the pros and cons of supporting a single code base instead of multiple applications? Did you ever consider splitting the front-end into separate functional areas?

I currently support 3 separate applications that address different areas (accounting, asset management, and loan servicing) that all link to the same SQL Server database. Over time there have been requests that combine the data from these areas and that require functions to be copied to two or all three applications.  
I am considering merging these 3 applications into one application to simplify support and eliminate the need to update a function in more than one place. These were also created over a 5 year period but my compiled sizes (*.accde) are smaller: 24 mb, 16 mb, and 48 mb. Due to duplicate code I'd expect the combination to be smaller than the sum.

Author Comment

ID: 40508075
It used to be 5 applications. I found it to be much easier to maintain 1 application; I also found that, for me, using .mda files was more problematic. However the biggest reason four 1 application was pressure from the client to do it that way and it has worked out extremely well so far. The only large applications that I regret are the .adp files, especially now that MS no longer supports them.
LVL 85
ID: 40508191
1059 versions / 7 years = 151.3 changes per year.
365 / 151.3 = 2.4 days/change

I love highly iterative development, but you're at the extreme!!
LVL 51

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40508658
I guess it depends on wether you run 32- or 64-bit Access.
I use 32-bit, and ran the code below.

Even with my Xeon and SSD equipped Lenovo workstation, it took an hour or so - I added 1000 modules a time and did something else than watching, so no exact figure - and the result is probably some arbitrary figure, because it stopped at:

    5449 modules

with an Out of Memory error => crash.

So you seem safe with 1K modules but I would certainly consider the option of splitting the frontend in two or more frontends dedicated different user groups.
Option Compare Database
Option Explicit

Public Sub CreateModule(ByVal ModuleName As String)

' Programatically create and name a new module in the current db.

    Dim NewModule   As Module

    ' Create the module.
    DoCmd.RunCommand acCmdNewObjectModule
    ' Set NewModule to be the new Module Object.
    Set NewModule = Application.Modules.Item(Application.Modules.Count - 1)
    ' Save, close, and rename the new module.
    DoCmd.Save acModule, NewModule.Name
    DoCmd.Close acModule, NewModule.Name, acSaveYes
    DoCmd.Rename ModuleName, acModule, NewModule

End Sub

Public Sub CreateModules(ByVal ModuleCount As Integer)

    Dim Index   As Integer
    Dim Count   As Integer
    Count = CurrentProject.AllModules.Count
    For Index = Count  To Count + ModuleCount 
        Call CreateModule("Test" & Format(Index, String(5, "0")))
        If Index Mod 10 = 0 Then
        End If
        Debug.Print Index
End Sub

Open in new window

LVL 75
ID: 40508662
You've got to wonder though ... if code in a module (vs a blank module) matters ?

And Gustav ... what if you reboot ... and continue running ... would more modules be added ?
LVL 51

Expert Comment

by:Gustav Brock
ID: 40508677
No. Access uses only about 60 MB and I have 10 GB free memory, so it is another ressource issue that causes this error.
If code were added to the modules, the max. count could be lower. Don't know.


Author Comment

ID: 40509074
I'm using the 32 bit version. I've got a long way to go before I get t 5449 modules. So I guess I'm safe for a while.

BTW, using the first release date of 04/20/2006 and 1059 versions I think it works out to a new release about every 2 work days. It's quite an interesting dynamic, i.e. I add features at a feverish pace and we remove features at a snails pace by moving them to a MVC web application.
LVL 51

Expert Comment

by:Gustav Brock
ID: 40509167
In Access 97 you would have been hosed.

I ran the test, and it failed after 909 modules. No crash, no memory error, though.
Interestingly, it ran in a few seconds only.

You'll need this modification:

   Count = CurrentDb.Containers("Modules").Documents.Count


Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question