How to make a large project?

[amorosik]

Sometimes these are projects with a few dozen forms, tables, and some code modules
But sometimes we are faced with projects that have hundreds of forms, hundreds of reports, dozens of modules even with a lot of code, and in this case we often get close to the limits of the Access environment

The question is: working on a project that is expected to grow continuously at the rate of a few dozen objects/month, how do you set things up from the beginning to allow this growth without preventing the correct overall functioning?
(i am obviously talking about the part relating to the user interface, reports and underlying code, the data will be held by a classic db server)

 

[June7]

Why would a db "grow continuously at the rate of a few dozen objects/month"? Once deployed, a well-designed db should seldom require major design changes.

 

[amorosik]

Why would a db "grow continuously at the rate of a few dozen objects/month"? Once deployed, a well-designed db should seldom require major design changes.

This means that the project grows by adding new forms and reports and code, after being released.

 

[Minty]

I have a system that we took over that had been continuously developed for 20 years.
It has a awful lot of forms, queries, and reports but is still only 30Mb as a front end including quite a lot of temporary local tables.

We still develop new functionality almost every couple of months, but never add more than a couple of forms to the overall process.
As long as the data is in a separate back end I doubt you would reach anywhere near the size limitations of a normal access file.

A truly massive application might reach 100Mb without data.

I also doubt that in a predominantly finished application you would add dozens of objects every month, unless it wasn't really finished.

 

[amorosik]

The question is: how to organize a project to avoid it reaching dimensions incompatible with the limits of the development environment
All other considerations are useless

 

[Pat Hartman]

how to organize a project to avoid it reaching dimensions incompatible with the limits of the development environment

Know your scope ahead of time??? You can't change the dimensions of the project to fit within the limits of your chosen platform. That's backwards. You may feel that ultimately Access is not the right platform for the project but due to money or time constraints you decide to start with Access but consider it a prototype and go right into the planning for the new product as you finish each new piece of the prototype.

 

[Minty]

I will repeat what I said.
The largest front end file I have ever worked on (NO data) was ~ 70Mb and it was a very large application that had numerous sections, invoicing, inventory, contract management, service call management, and workshop repair management.

We could have easily split considerable amount of those into separate dedicated databases with a common backend, but that wasn't how it developed.

So to answer your question - and repeat what Pat said - get a scope of development and agree it beforehand.

As far as Access is concerned I would be really surprised, in fact staggered, if you would exceed it's file size restrictions with just the front end on the data.

 

[amorosik]

Know your scope ahead of time??? You can't change the dimensions of the project to fit within the limits of your chosen platform. That's backwards. You may feel that ultimately Access is not the right platform for the project but due to money or time constraints you decide to start with Access but consider it a prototype and go right into the planning for the new product as you finish each new piece of the prototype.

No, the final size is not known

 

[amorosik]

I will repeat what I said.
The largest front end file I have ever worked on (NO data) was ~ 70Mb and it was a very large application that had numerous sections, invoicing, inventory, contract management, service call management, and workshop repair management.

We could have easily split considerable amount of those into separate dedicated databases with a common backend, but that wasn't how it developed.

So to answer your question - and repeat what Pat said - get a scope of development and agree it beforehand.

As far as Access is concerned I would be really surprised, in fact staggered, if you would exceed it's file size restrictions with just the front end on the data.

You don't need to repeat it, it's perfectly clear
And indicating the size of the largest file you've worked on is of no use in understanding how to deal with the problem

I asked how to organize a project (also understood as a set of multiple Access files or similar) so that it can grow in size (number and size of forms, reports, modules) without exceeding the limits of the development environment

 

[The_Doc_Man]

a project that is expected to grow continuously at the rate of a few dozen objects/month

The REAL translation: That is the fastest growth rate I can maintain 'cause I can only do so much per month. I.e. you don't expect to have ANY help on this project. You will be doing it yourself. I've been in the middle of one of those. It's like the vortex of a giant whirlpool that sucks you in and you can't swim out no matter how hard you try. You become convinced you will drown in this mess that you spun up for yourself.

If these "few dozen objects per month" include objects that probably should be accessible via a query to filter by date, department, product or project name, cost, etc., you shouldn't have incredibly many queries. Reports? Maybe. Modules? You could in theory have nearly 2 GB worth of modules (2 GB minus system overhead) since you can link to other objects in a second file, but what I've done in the past is break the modules up into topics like Word Support, Excel Support, Statistics Support, Text Parsing, etc. Makes them easier to maintain and to find.

The REAL question to ask is the follow-up to the advice you already have been given. You need to estimate the scope and duration of this project and then consider how much, if any, help you can get to speed things up. (Which should be interesting since VBA projects are hard to share given the scope of individual routines as being too small to share.)

In the end analysis, you have to ask whoever started this project if they can live with how long it will take. If your boss wants it bad enough and fast enough, you might get some help of some kind. If you are doing this because YOU PERSONALLY are considering some sort of massive commercial product, I have two comments. First, good luck. Second, don't forget to leave yourself a way to make money on which to live while your personal pet project is underway, because it will produce no income for quite a while.

Either way, my usual advice about building a "project bible" applies. If you don't build a document to explain / explore the scope and details of what needs to be done, there will come a time when you will have to stop and answer those scope questions anyway, because you will be at a point where you MUST know when to stop what you are doing and move on to something else. The project bible is a pay-me-now/pay-me-later thing and you will bless the day you decided to do that first, or curse the day you avoided it.

 

[amorosik]

What exactly is the purpose of all these considerations?
I don't see any useful indication to clarify the question posed initially

 

[The_Doc_Man]

how do you set things up from the beginning to allow this growth without preventing the correct overall functioning?

Planning, planning, and more planning. (See above, "project bible".) There is NO SUBSTITUTE for a lot of brain work in planning out a road map that will get you from where you start to where you want to go. Planning doesn't ONLY encompass WHAT you do but it also must consider HOW you planned to do it. Planning and detailed problem analysis will TELL you when you have a bottleneck or a potential conflict or a situation in which something you need to do will affect something you have already done or are about to do.

Let me offer this analogy. When you are driving on a busy highway, you need to watch what is going on ahead of you. But you ALSO need to be aware of what is going on around you and what is happening behind you. Otherwise, you will have an accident from something you didn't see coming. AND if you are unsure of your route, having a map that you can study will make the journey smoother and safer. Don't focus only on the road. Focus on places around you that will cause congestion. Focus on places that will incur extra traffic. Treat it, not like a trip to the grocery store but as a long-distance vacation. Plan out your milestones.

Projects often fail, not because of the lack of technical expertise of the implementer(s) but from the lack of planning by the manager(s). A good plan, if laid out correctly, will NOT limit itself to coding concepts., but also on resource concepts, personnel concepts, quality control concepts, ... I could go on, but I hope that gives you the idea.

 

[Minty]

I asked how to organize a project (also understood as a set of multiple Access files or similar) so that it can grow in size (number and size of forms, reports, modules) without exceeding the limits of the development environment

And you are missing the point that the chances of you or anyone else developing an Access Front End that exceeded the size "Limits of the Environment" are probably smaller than me winning the Lotto. Twice. In a week.

A 1Gb front end without any data stored locally would be something I would like to see. You asked about size I replied about the size.

 

[CJ_London]

t sometimes we are faced with projects that have hundreds of forms, hundreds of reports, dozens of modules even with a lot of code, and in this case we often get close to the limits of the Access environment

I have developed such an application - 150+ forms, 50+ reports, 30+ modules. 1000 users globally, linked to 3 different sql servers. In total the FE was around 35mb - so could have been roughly 50 times bigger. So no idea how you often get close to the limits of Access. If this is your experience, you must be doing something wrong.

how do you set things up from the beginning to allow this growth without preventing the correct overall functioning?

A very open ended question - by good management principles

A bigger concern for me would be memory management - bringing huge amounts of data across could blow a users memory - but again a matter of good design and an awareness of the limitations of your users devices

 

[Edgar_]

You can get cross platform ideas here:
https://refactoring.guru/refactoring

These are Access' limits:
https://support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

Here's a few ideas:

• Save your images and attachments as paths
• Divide the app in multiple frontends if required
• Names should be long enough to convey their purpose and short enough to make the code readable. Also, keep in mind there's a limit of characters for SQL statements in some contexts
• Forms and reports should be simple enough, you don't want to open too many connections, forms don't need to be permanently open, you can close them, also, there are navigation forms, subform controls and there's the DoCmd.BrowseTo method to close forms in a graceful and friendly way
• Use the proper data type for your columns
• Index your data properly, make a few tests to check your performance
• Put your business rules in a separate place from common application rules to allow reusability
• Paginate your data, don't load huge tables, nobody can read the 100k records of that table
• Only show what the user needs, not just because of performance, but for security reasons
• Normalize your tables, if you find yourself adding columns to a table, maybe those columns should be records, maybe you could store those columns in one column and split them later or maybe you should use another type of database
• Favor working with views if you have multiple tables
• Document your code
• Document your application
• Add a "Tour" feature for your forms
• Keep your forms simple, not just because of the maximum connections, but because you will have to figure out your forms in one year or more and you WILL forget where things are, so it's best if you make it intuitive, it's also a best for your user
• Use anchors to expand your controls, people have different screen sizes
• You got more than half a meter of height to design that form, figure out whether it's better to make it vertical or place your controls in a tab control, but don't make your user have to scroll horizontally, it's a PITA
• Make sure you can re-establish service quickly in case of corruption, have scripts and tests available
• Have a form for managing the other forms quickly

Finally, make regular backups and keep a log of things a user does to avoid going to jail and catch evil-doers before anything bad happens.

 

[amorosik]

And you are missing the point that the chances of you or anyone else developing an Access Front End that exceeded the size "Limits of the Environment" are probably smaller than me winning the Lotto. Twice. In a week.
A 1Gb front end without any data stored locally would be something I would like to see. You asked about size I replied about the size.

Maybe you didn't read the request well
I asked:
"..how do you 'set things' up from the beginning to allow this growth.."
"..how to organize 'a project' to avoid it reaching dimensions incompatible.."

I never asked how to work on a file exceeding the limits imposed by the environment you are working on
It is obvious that if some limits exist, you cannot exceed them

I asked how to organize a project (where by 'project' I do NOT mean a single file) so that it can support a growth that cannot be defined from the start
It could be:
1- it can be done, and then let's see how to do it
2- it cannot be done, and then never mind

The fact that in your experience it has never happened, does not mean that we are in the second of the two options

 

[amorosik]

I have developed such an application - 150+ forms, 50+ reports, 30+ modules. 1000 users globally, linked to 3 different sql servers. In total the FE was around 35mb - so could have been roughly 50 times bigger. So no idea how you often get close to the limits of Access. If this is your experience, you must be doing something wrong.

Thanks for sharing your experience
But this does not contribute anything to the question posed initially

A very open ended question - by good management principles

A bigger concern for me would be memory management - bringing huge amounts of data across could blow a users memory - but again a matter of good design and an awareness of the limitations of your users devices

Yes, user memory management can quickly become a problem, and so wanting to pre-view the possibility of monitoring, how to do it?

 

[amorosik]

You can get cross platform ideas here:
https://refactoring.guru/refactoring

These are Access' limits:
https://support.microsoft.com/en-us/office/access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

Here's a few ideas:

• Save your images and attachments as paths
• Divide the app in multiple frontends if required
• Names should be long enough to convey their purpose and short enough to make the code readable. Also, keep in mind there's a limit of characters for SQL statements in some contexts
• Forms and reports should be simple enough, you don't want to open too many connections, forms don't need to be permanently open, you can close them, also, there are navigation forms, subform controls and there's the DoCmd.BrowseTo method to close forms in a graceful and friendly way
• Use the proper data type for your columns
• Index your data properly, make a few tests to check your performance
• Put your business rules in a separate place from common application rules to allow reusability
• Paginate your data, don't load huge tables, nobody can read the 100k records of that table
• Only show what the user needs, not just because of performance, but for security reasons
• Normalize your tables, if you find yourself adding columns to a table, maybe those columns should be records, maybe you could store those columns in one column and split them later or maybe you should use another type of database
• Favor working with views if you have multiple tables
• Document your code
• Document your application
• Add a "Tour" feature for your forms
• Keep your forms simple, not just because of the maximum connections, but because you will have to figure out your forms in one year or more and you WILL forget where things are, so it's best if you make it intuitive, it's also a best for your user
• Use anchors to expand your controls, people have different screen sizes
• You got more than half a meter of height to design that form, figure out whether it's better to make it vertical or place your controls in a tab control, but don't make your user have to scroll horizontally, it's a PITA
• Make sure you can re-establish service quickly in case of corruption, have scripts and tests available
• Have a form for managing the other forms quickly

Finally, make regular backups and keep a log of things a user does to avoid going to jail and catch evil-doers before anything bad happens.

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

 

[CJ_London]

I see the question has changed.

Unfortunately the revised question does not contribute anything to understanding the context of the question, particularly since you have dismissed all previous answers without explaining why you have dismissed them and then made your own suggestions which are pretty much the same

Your perceived limits of access seem very low compared to reality and for such a large project you appear to be envisioning, perhaps access is not the development tool to use

With regards memory management - suggestions have already been made and plenty of examples can be found on line if you ask the right questions.

 

[xavier.batlle]

The question is: how to organize a project to avoid it reaching dimensions incompatible with the limits of the development environment
All other considerations are useless

The maximum number of modules (counting forms and reports with modules) in a database is about 5000 according Colin Riddington's article(according Microsoft documentation is 1000). Moreover, you can organize your application using libraries to store some objects.
I have an application where the forms and reports are stored in the main database, but some are also stored in the database library.