Solved

Max # of Modules in Access 2013 Desktop Applicaiton

Posted on 2014-12-17
21
190 Views
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?
0
Comment
Question by:rmk
  • 9
  • 5
  • 3
  • +3
21 Comments
 

Author Comment

by:rmk
Comment Utility
The last sentence had a type and should have been "Does anyone know the correct maximum number of modules?"
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:rmk
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:rmk
Comment Utility
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
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Just be SURE you backup *before* you Decompile ... because Decompile - although very rare ... can FUBAR your db.
0
 

Author Comment

by:rmk
Comment Utility
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
 
LVL 84
Comment Utility
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
 

Author Comment

by:rmk
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rmk
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Curious ... what is the Compacted & Repaired size of the Front End ?
0
 

Author Comment

by:rmk
Comment Utility
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
 
LVL 3

Expert Comment

by:RCUllrich
Comment Utility
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
 

Author Comment

by:rmk
Comment Utility
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
 
LVL 84
Comment Utility
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Comment

by:rmk
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
#deleted rowsc access 2010 backend 4 32
MS Access Bound Objects. 6 28
MS Access 2010 Form Building 3 21
Access MDB/PDF 21 28
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now