Max # of Modules in Access 2013 Desktop Applicaiton

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?
rmkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rmkAuthor Commented:
The last sentence had a type and should have been "Does anyone know the correct maximum number of modules?"
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ?

mx
0
rmkAuthor Commented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.
0
rmkAuthor Commented:
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).
0
Nick67Commented:
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
https://support.office.com/en-ca/article/Access-2010-specifications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854

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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just be SURE you backup *before* you Decompile ... because Decompile - although very rare ... can FUBAR your db.
0
rmkAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
rmkAuthor Commented:
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.
0
rmkAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Curious ... what is the Compacted & Repaired size of the Front End ?
0
rmkAuthor Commented:
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
0
RCUllrichCommented:
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.
0
rmkAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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!!
0
Gustav BrockCIOCommented:
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
            DoEvents
        End If
        Debug.Print Index
    Next
    
End Sub

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ?
0
Gustav BrockCIOCommented:
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.

/gustav
0
rmkAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.