How to make a large project?

[Minty]

You can understand exactly what I wanted by reading post1

We did, very quickly afterwards you wrote in post #5:

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

Read carefully the last line that YOU WROTE , dismissing any reference to any existing applications, experience, or what it was you were doing that would involve adding dozens of objects every month.

We asked, you diverted.
We asked again, you obfuscated.

We gave up.

 

[CJ_London]

Sorry but maybe I explained myself badly

All the time, all the time

So you have an app- after decompiling and compacting what is the file size? My guess is nothing like 2gb so likely that is not an issue

Withe regards forms/reports - how many actually have a module? Easy to see in the vba navigation window.

 

[The_Doc_Man]

Did you inadvertently leave out a UAT environment when describing your process? - so the users have a chance to review/verify the functionality expected, implement and test urgent fixes and set up a change log and to get sign off from the users/project owner?

Fair question. Testing of that type was informal but the project's owner (my direct supervisor) had frequent check & sign-off opportunities. Some of my users agreed to look at things and DID provide feedback. But there was no fifth environment for user acceptance testing. Part of the issue was that we were feeding monthly or weekly reports and acceptance testing was ultimately dependent on whether our reports passed the requirements of our system security overseers. The interfaces for data updates DEFINITELY were user-tested and were passed through multiple stages until we got the first "real" system up. Once the production environment went live, testing of new features was a bit trickier.

 

[The_Doc_Man]

Already now they do not appear to be those described in the Microsoft documentation

If you look around various sites regarding a limit on the number of records, the answer is that it depends on the number and size of fields as well as overhead issues - like how many other tables share the same file. The ultimate limit is that number of records that which, if exceeded, would cause the individual file to exceed 2 GB. Any combination of records that blows the container's limit is the actual limit. You want "busy" forms? You'll have room for fewer of them. You want "lean, sparse" forms? You can fit a lot more of those. You have to know how to read that documentation. The answer is quite clear. Access has limits. It doesn't matter which way you attempt to pass the limit, but if you pass that limit, you are done.

So... how many records can you have? I don't know because I can't see your schema.

Some limits are hard and solid, like the number of fields in a record or the maximum size of a single form in inches. Some are interdependent on the makeup of the thing testing that limit. You are going to have to live with "amorphous" limits.

 

[amorosik]

All the time, all the time
So you have an app- after decompiling and compacting what is the file size? My guess is nothing like 2gb so likely that is not an issue
Withe regards forms/reports - how many actually have a module? Easy to see in the vba navigation window.

My current specific application is of no importance
The important thing is how to make a project with a number of objects that exceeds the size of the objects contained in a single Access file

 

[amorosik]

Did you inadvertently leave out a UAT environment when describing your process? - so the users have a chance to review/verify the functionality expected, implement and test urgent fixes and set up a change log and to get sign off from the users/project owner?

What you mean with 'UAT enviroment' ?

 

[gemma-the-husky]

I still don't believe that any database needs to grow month after month, unless it is caused by normalisation issues, and duplication of processes. Therefore the ever expanding project could be dealt differently without needing to add new forms perpetually.

Additionally, as already mentioned,.a project can most likely be split into sections that are really independent of each other.

 

[CJ_London]

UAT=User Acceptance Testing

you may know it as something else.

For anything professional it is the final step before releasing the app to production (After the app has passed all the user tests)

The developer should be aware of what those tests are and build the app accordingly. The tests are typically specified from a mixture of the original specification (which might be modified by CR's (Change Requests) and US's (User Stories) - clearly the developer will do their own tests before releasing to UAT but since they are the developer they can be blind to some of the potential issues. For example, I mainly navigate with the mouse and rarely use keyboard navigation - I can forget that some users are the other way round and rarely use the mouse - I can be aware and test, but can still miss something

 

[Pat Hartman]

And therefore I would like to modify the structure of the project making it capable of supporting a growth,

This is more relevant to your schema than anything else. If your schema is not properly normalized, then nothing can help you.

 

[The_Doc_Man]

My current specific application is of no importance
The important thing is how to make a project with a number of objects that exceeds the size of the objects contained in a single Access file

Several posts ago I gave you this answer. Identify the parts by thorough analysis to identify functions that can be segregated. Design your project to perform that segregation into discrete .ACCDB files. Check for the interactions between the distinct parts. PLAN and specify the nature of their interactions, which COULD be two FE files sharing the same BE file, or it could be an external transaction file written by one app and read by another. Recognize EACH / ALL of those cases where the different parts overlap and carefully define their method of overlap.

Nowhere in this brief explanation did I limit the number of Access back-end or side-end or front-end files would be used. Your only limit will be the machine or machines used to host this mega-app. You want details? This is YOUR project. YOU have to fill in the blanks.

 

[GaP42]

Several posts ago I gave you this answer. Identify the parts by thorough analysis to identify functions that can be segregated. Design your project to perform that segregation into discrete .ACCDB files. Check for the interactions between the distinct parts. PLAN and specify the nature of their interactions, which COULD be two FE files sharing the same BE file, or it could be an external transaction file written by one app and read by another. Recognize EACH / ALL of those cases where the different parts overlap and carefully define their method of overlap.

Nowhere in this brief explanation did I limit the number of Access back-end or side-end or front-end files would be used. Your only limit will be the machine or machines used to host this mega-app. You want details? This is YOUR project. YOU have to fill in the blanks.

In essence it becomes / is similar to systems integration project - multiple (departmental) systems that need to exchange data. This may be done through the one database/ shared tables, however clear rules around which application is the owner of the data in the sense of right to create modify delete, or through real-time data exchange - ie messaging - through use of a defined standard such as HL7 as used in health apps, where transactional events trigger the exchange (and automated requests) or through supply of data in batch extracts, triggered by events - to an aggregating application supporting reporting / performance analysis tasks.

 

[The_Doc_Man]

In essence it becomes / is similar to systems integration project - multiple (departmental) systems that need to exchange data. This may be done through the one database/ shared tables, however clear rules around which application is the owner of the data in the sense of right to create modify delete, or through real-time data exchange - ie messaging - through use of a defined standard such as HL7 as used in health apps, where transactional events trigger the exchange (and automated requests) or through supply of data in batch extracts, triggered by events - to an aggregating application supporting reporting / performance analysis tasks.

When I worked with the U.S. Navy, we had 18 different computers that exchanged data. When I started, networking wasn't QUITE as advanced as it later became, so we had a daily, weekly, or other periodic "tape cut" where we built a transaction file that would be sent to some agency and we would get back a response to that file PLUS their proposed changes. As time passed, we changed to use SFTP as our transfer method rather than putting sealed tape reels in a Navy truck to schlep them to airplanes for transport to other systems. Networking radically reduced our operational costs because before then, the sailor who drove the trucks was on special duty - classified data courier. Because these tapes also involved personal info, we had requirements that the sailor would be armed or would have an armed guard. Involving extra, higher-ranked thus higher-paid people.

Everyone knew which machine was "authoritative" and deferred to its files as gospel. For instance, my assigned major system was U.S. Navy Reserve Personnel issues for duty station assignment, rank/rate changes, billets, and dependent data. We would send our data to, for instance, DFAS - Defense Finance Accounting System - for any monetary transactions, for which THEY were authoritative. Another department handled training requirements authoritatively.

In line with how that applies to this thread, we rarely had trouble processing what our own people entered. Our problems were with the interface files, the overlapping points of contact, and situations where someone violated procedures leading to transaction rejections. amorosik's problem will be to find HIS points of overlap, closely define what transactions are allowed, and assure that both apps "understand" the rules.

 

[Cotswold]

Stages of a Contract
Enthusiasm
Disillusionment
Panic
Search for the guilty
Punishment of the innocent
Praise and advancement for the uninvolved

At the end of the day, how good the result is will depend on how good you are.
If you read the postings in this thread and understand them, then that will be beneficial.

 

[gemma-the-husky]

UAT=User Acceptance Testing

you may know it as something else.

For anything professional it is the final step before releasing the app to production (After the app has passed all the user tests)

The developer should be aware of what those tests are and build the app accordingly. The tests are typically specified from a mixture of the original specification (which might be modified by CR's (Change Requests) and US's (User Stories) - clearly the developer will do their own tests before releasing to UAT but since they are the developer they can be blind to some of the potential issues. For example, I mainly navigate with the mouse and rarely use keyboard navigation - I can forget that some users are the other way round and rarely use the mouse - I can be aware and test, but can still miss something

Having said that, you also have to realise that users are likely to be conditioned by their previous experience and won't necessarily recognize and value all improvements at first sight.

The real problem is that developers understand development, but don't understand business practice, and the needs of a specific organisation. Users don't understand what they really require.

So developers might deliver a product that meets the specification, but really doesn't satisfy the clients real requirements.

And that's why UAT is often a waste of time. Sorry to have a jaundiced view.

It's also how many projects end up in the way @Cotswold just described.

The best way to complete a project is to get a contractor who understands your business and substantially leave them to it. The trouble is finding the right contractor.

 

[gemma-the-husky]

Yes of course, this does not mean that I have one super-large project
But this consideration has no importance in order to solve the problem posed
I could actually have the problem to solve, or I could not have it, this thing has no importance in order to solve the problem posed
Which honestly seems to me to be of interest to everyone
In this sense I do not understand the reason why I see answers that try to belittle the need to use a more or less standard technique that allows you to overcome the limits (not very large I would say) that are imposed by the single Access file

It's only of academic interest because I expect a vanishingly small number of us have ever been involved in projects that conceivably could get big enough to challenge the limits of access, and no doubt we would have a strategy to deal with it if it became necessary, or more likely well before it got that big. But we wouldn't have projects that expanded every month for no particular reason, which is what I thought you were saying.

It's one thing expanding because the client has additional requirements. You made it sound like a real project was expanding, possibly because of poor design.

Yes, I've been involved in rewriting projects that were poorly written and queries got too big to run. Once they were rewritten in a properly normalised fashion, the problems went away, and more importantly the rewritten project was able to deal with things the client had never considered previously.

And the main reason the project was designed so poorly was because the client said "I want this", and the developer gave them what they asked for, instead of what they really needed.

 

[The_Doc_Man]

And the main reason the project was designed so poorly was because the client said "I want this", and the developer gave them what they asked for, instead of what they really needed.

In a very strong sense, you take on the responsibility of telling the customer he's an idiot if you take the job even AFTER he asks for idiotic things. How diplomatically you can do so is up to you.

 

[Pat Hartman]

I spent 30 years designing and developing and managing multi-million dollar mainframe projects before coming to Access. Not a single one of them would challenge the capabilities of Access.

 

[gemma-the-husky]

In a very strong sense, you take on the responsibility of telling the customer he's an idiot if you take the job even AFTER he asks for idiotic things. How diplomatically you can do so is up to you.

Well you can give him what looks like he asked for, but design in it a proper normalised way, so that when the client wants more complications you have a framework that enables the growth.

 

[Edgar_]

Wow, 138 posts now.

OP, are you ever going to mark this as solved?

Surely trying to understand what the real limits of the objects that can be managed by a single Access file are, because from some experiments already performed it seems that they are not so well defined

Because they are impossible to define. Too many factors are at play.

If someone tells you it's 1k forms, what if all of those forms have 1 label each? Do you think your computer is going to struggle? Likely not, if you have a high-end computer, but if you have a computer running with the typical specifics to operate in 1995, it is very likely to give you problems.

On the other hand, what do you think would be the result of having 1000 open forms that:
- have X web browser controls connected to websites that hug the CPU
- have Y tables with one million records loaded, each
- have Z combo box controls with complex nested queries each grabbing tens of thousands of records each
- have N subforms, with multiple visual effects and timers running at the same time?
- have a few media players playing 4k videos
- and for every move of the mouse, a sound is played and all forms are reacting to each other?

Now, what do you think it would be the result if you try to operate in that environment with a computer with 256MB of RAM and 3GB of disk space? On the other hand, do you think a carefully crafted setup that can withstand the computing power of serving 100 million users worldwide generating AI videos would even blink with that?

Will you still say that the specifics have no importance for the question posed?

These are the initial steps, and then all the others related to the techniques to allow the project to be updated without causing disturbance in the normal operation of the user

If you want to know what's going to disturb normal operation, define normal operation.

 

[amorosik]

Wow, 138 posts now.

OP, are you ever going to mark this as solved?

Because they are impossible to define. Too many factors are at play.

If it is not possible to use the 'number of objects' to understand the technical limit that can be contained in a single Access file, then it means that other conditions will have to be checked, such as the memory used by that single Access file during operation, or other conditions still