There are technical limits for the development environment, in this case Access, beyond which you can not go
My question is aimed at understanding how it is possible to create a project where, while working within the imposed limits, it is possible to increase the number of forms / reports / modules in a transparent way for the user
One possibility could be to create a single Access file that contains only the access menu to the programs (form + modules)
And then many other individual Access files for the operating procedures (form + report + modules)
Each one that respects the limits imposed by the version of Access with which it is being developed
But there are several problems to solve, the connection to the db tables, any common functions, the physical update of the db, etc ...
I asked the question thinking that this eventuality could have already been addressed and resolved in some way
Again, my answer is superficially the same. Planning is the
sine qua non of handling large projects. There was the 6P rule that I recall when I worked for the pipeline control systems company TANO some years ago... Pathetically Poor Planning Produces Problematic Projects.
It seems that you HAVE done at least some planning, though, because you at least recognize the overall magnitude of the project. I see only changes in details but your answer will remain the same. But let's look at planning of a DB project.
If you can identify them well enough, these items need to be categorized into groups:
1. Importance: Ultra High - so important that you can't even have users yet until you have them. (Basic tables fall into this category.)
2. Importance: Selectively Ultra High - important enough to prevent use if you don't have it - but only to specific users
3. Importance: High - important in the long run
... I think you get the idea.
At the bottom of the list, ... Importance: I intend to get around to this eventually.
If you DON'T have that project bible of goals, inputs, outputs, and formulas, you WILL NOT be able to make this prioritized list.
Now itemize each item along with its dependencies and priority. Identify every case where in order to run procedure 1 you must first have built table 1 and query 1. In order to perform process 3 you must first run procedure 1 and then process 2. And so on...
This will be a standard project-flow chart, a Gannt chart. MS Project can do this; other commercial products abound. But without the planning and problem analysis, you can't develop a chart to help with the schedule part of the planning.
As to updating issues, what I did for my ongoing project was I tried to group my updates according to two basic alternatives: Updates that only affect the front end, and updates that affected the back-end. (It didn't actually matter if a back-end update was accompanied by a front-end update.)
For FE-only, see the methods used by Pat Harman that automatically updated the FE every time you launched it, by using a little batch job to copy the FE from a distribution directory. The users wouldn't even know it had occurred unless they tracked some visible indicator of version number. Or unless you told them ahead of time.
For BE updates, with or without FE updates, it is SUPREMELY important to have a scheduled down-time during which you can
1. Make a backup of the current BE file - a place you can get back to if the update fails.
2. Remove the BE file from the shared folder. That will prevent the FE from working while you are messing with the guts of your app.
3. Verify by examination and by simple testing that you have, in fact, made the update.
4. IF there was a parallel FE update, make that first.
5. Move the updated BE back to the shared folder.
At this point, you are back on the air again.
Now, the trick is to do your updates, segregating FE changes into those that are adding new features compatible with the existing schema and those that require the BE update. Then schedule those changes as noted above, realizing that the no-change-to-schema updates can occur darned near any time.
Setting this up so you CAN do this? In my Navy project that was pretty darned big, I had four environments. I'm not saying you absolutely MUST do it this way, but ... it worked for me.
There was a DEV folder (actually set of folders) where I did the modifications in isolation. The DEV copy of the FE pointed to a DEV copy of the BE, and NEITHER was visible to any users. When I was ready to do more strenuous / rigorous testing, I moved a copy to the TEST set of folders. Again, users never played with those folders. IF I had to go back to the drawing board, no harm and thus no foul. That was where I did compliance testing, destructive testing, ... whatever was needed. TEST files were simply snapshot copies of DEV files.
When I had something ready to become public, I had a third set of folders called STAGING. In the staging folder, I did some final polishing and preparation steps such as turning off the ribbon, blocking use of the SHIFT key on startup, and other things to secure the app as a whole. The only testing that occurred during staging was to verify that the process of securing the DB was correct. Finally, when all was ready to go...
If it was FE-only then the STAGING FE was moved to the PROD folders and pointed to the BE file. The "public" copy of the FE already in PROD would first be renamed to include a date like SADB_2015_11_31.ACCDB and then the new FE would then go into its place. This worked OK because users were always working from the copy made by that batch distribution script. Nobody EVER ran from the script in the shared folder. In fact, if anyone tried, the app would abort and send me an e-mail so that I could send the user a nasty-gram.
If it was a BE update, then it got trickier. There, I had to build a secondary procedure using DDL to make updates to the schema because I wanted to preserve the current data. So that meant that any time I was adding fields or changing field sizes or stuff like that, there was an update script. Oddly enough, adding new tables was easier since nobody would ever know about them even indirectly. I could just import the new tables from the STAGING copy, erasing content if needed or leaving defined content (such as, e.g. a translation table) in place.
In summary, how do you manage this? Treat it like you are a physician performing surgery on a beating heart. Plan every step ahead of time, and then, like in a surgical operating room, have your materials already laid out and ready to use. Set aside the time, don't give in to someone else's false ideas of how important THEIR report is to get it out on a particular date and time. Check around, decide when is the best time to do it, and then damn the impatient users, full speed ahead on the updates.
This is the closest I can come to explaining the kind of thinking and planning and supporting structure needed for a project of your size.
