How to make a large project?

[amorosik]

You don't. The "without preventing correct functioning" part hides a tricky "gotcha" called "change orders." What you are asking is, in essence, a two-pass (or higher) algorithm. To prevent roadblocks you must be able to see the future. You are asking me to violate time symmetry. So... no, you can't do this. Reality ALWAYS intrudes on projects. (See also the third law of thermodynamics, having to do with open vs. closed systems.)

No, seeing the future would be nice (but not that much) but it's not possible
It is enough for me to understand how to organize things to obtain a project that can manage X+1 objects, where X is the maximum number of objects that a single Access file can manage
I think it is the word 'project' that confuses things, because by 'project' I mean any set of Access files (or other files type) that can collaborate with each other, while I see that many answers insist on indicating the limits of the single Access file

 

[Edgar_]

It is enough for me to understand how to organize things to obtain a project that can manage X+1 objects, where X is the maximum number of objects that a single Access file can manage

What is the progress of your understanding so far? Access has tools to share code across frontends, it also has tools to import/export frontend objects if necessary. That alone gives you endless capacity.

 

[amorosik]

What is the progress of your understanding so far? Access has tools to share code across frontends, it also has tools to import/export frontend objects if necessary. That alone gives you endless capacity.

So far it seems to me that, assuming you want to create a large-form-report-code-project system, the main problems are two:
- the limits on the number of objects that can be managed within a single Access file
- the limits of memory that a single Access file can use

In particular on the second point, it is not yet clear to me whether using 64-bit versions of Access these limits of usable memory can be exceeded, and above all whether these limits should be applied overall to all the procedures (one memory sector per all Access file) active at a certain time or should be applied for each procedure (one memory sector for each Access file) in a distinct way

Yes, the use of export/import procedures for single objects on text file is already foreseen (Tim Abell's Vcs)
But I do not think this thing has any importance in relation to the 3d object

 

[CJ_London]

The problem is I have two objects, we'll call one a mouse and the other an elephant. You seem to be assuming that any object consumes the same amount of memory, the same amount of resources. So an 'object count' of 2 will be the same whether you have two mice or two elephants or one of each. Whereas the resource required for a mouse object is completely different to an elephant object.

So aside from the limit on number of coded objects in a single access file which is well documented - how do you expect to determine when you are approaching the memory limit - which as already stated, is more a problem for the machine/windows - works fine on your machine with 32Gb+ of memory, but really slow on a users 8Gb machine. Common advice when designing forms is to design to the smallest monitor. Perhaps you should apply the same principle to memory - take out 24Gb from your machine and develop from that.

You want to know some memory stats whilst your code is running - see these links

Redirecting

answers.microsoft.com

and

MSAccess - Total & Avail Memory Stats?

I am currently using the following to report the total memory of the various clients using my MSAccess app. I am seeing a wide range of users: 2GB to 32GB. In addition to total memory, I pres...

stackoverflow.com

 

[The_Doc_Man]

OK, one last try.

Your problem HAS changed a bit from as initially asked / implied, to include the possibility that you COULD have multiple dedicated-purpose app files. (That was NOT evident in the original question.) I will take a stab at this aspect of your project question.

You will have multiple moving parts. As I said before, a detailed planning stage is MANDATORY for this to have any chance. Find how many moving parts / distinct apps you anticipate on having. You MUST do this because you are preparing to do a divide-and-conquer analysis. You will need to know into how many parts this problem divides so you can provide for each dedicated part.

Now, let's say you did that. You have an educated idea of how many distinct apps you will need. The divisions might be along departmental lines. Or your division might be regional with different legalities and requirements for different regions. The division might be topic-related. Doesn't matter HOW or WHY it slices up into parts. The important part is that it does. The next step, now that you have a count on apps, is to decide where the apps overlap in any way, thus suggesting that they will need to share data in some way. This is where the serious planning comes in.

From a previous discussion, I made it clear that multiple Windows tasks CANNOT share RAM-type memory. While that is not 100% true, for Access it might as well be cast in concrete because we have no visibility in MSACCESS.EXE and cannot change how that works. But these hypothetical distinct apps CAN share data files, both conventional and back-end.

Therefore, the next step is to design the files that will be shared. If you can have a common back-end that will NOT be mapped by every task, but WILL be mapped by more than one task, design and LOCK DOWN THE DESIGN of what goes in that one back-end that will be shared. Also consider that building a text-oriented transaction file might be a preferable option in some cases as a way to minimize your use of the limited space of the .MDB / .ACCDB file. For EVERY COMBINATION of apps that must share data between two isolated apps, BE PREPARED to share them in files that might not be inside of Access. For example, a transaction file that is essentially a .CSV text file. And be prepared to rename or erase or otherwise mark that file as "USED" in some way so that you don't try to import it twice. How you do that specifically has to be up to you.

IF you use a divide-and-conquer method, two things happen. First, though the problem might expand, you CAN isolate the parts that are affected by this hypothetical expansion. Second, while this won't prevent some disruption, it allows continuing use of parts not affected by catastrophic changes. AND it won't nearly as often require MASSIVE changes - because you have already divided the problem into pieces-parts. This is the key, though. You CAN'T do the above without enough planning to identify the overlaps and non-overlaps, which means you have to identify the distinct parts that will get their own dedicated sub-apps. So my original discussion on extensive planning remains in place and I stand behind it.

One last thought: IF you were going to ask the question about a project that cannot be divided into applicability segments, go away because that question if asked regarding a monolithic project is unanswerable even in the abstract.

 

[gemma-the-husky]

@amorosik

I really think you are talking about an academic/esoteric situation that is most likely unable to ever arise.

ie. Can you comprehend a system that is too big for access development? I just can't personally. I have systems that need more space for millions of records, but that's a different issue. the biggest database I have with thousands of objects is still smaller than 100mb.

 

[amorosik]

@amorosik

I really think you are talking about an academic/esoteric situation that is most likely unable to ever arise.

ie. Can you comprehend a system that is too big for access development? I just can't personally. I have systems that need more space for millions of records, but that's a different issue. the biggest database I have with thousands of objects is still smaller than 100mb.

No, is a true project
Million of records not impact on the question posed, the data db is external to the application
You write of 'biggest database with thousand of object'
Can you indicate how many forms, reports, modules this application is composed of?
Does it work on Access 32bit or 64bit?

 

[amorosik]

OK, one last try.

Your problem HAS changed a bit from as initially asked / implied, to include the possibility that you COULD have multiple dedicated-purpose app files. (That was NOT evident in the original question.) I will take a stab at this aspect of your project question.

You will have multiple moving parts. As I said before, a detailed planning stage is MANDATORY for this to have any chance. Find how many moving parts / distinct apps you anticipate on having. You MUST do this because you are preparing to do a divide-and-conquer analysis. You will need to know into how many parts this problem divides so you can provide for each dedicated part.

Now, let's say you did that. You have an educated idea of how many distinct apps you will need. The divisions might be along departmental lines. Or your division might be regional with different legalities and requirements for different regions. The division might be topic-related. Doesn't matter HOW or WHY it slices up into parts. The important part is that it does. The next step, now that you have a count on apps, is to decide where the apps overlap in any way, thus suggesting that they will need to share data in some way. This is where the serious planning comes in.

From a previous discussion, I made it clear that multiple Windows tasks CANNOT share RAM-type memory. While that is not 100% true, for Access it might as well be cast in concrete because we have no visibility in MSACCESS.EXE and cannot change how that works. But these hypothetical distinct apps CAN share data files, both conventional and back-end.

Therefore, the next step is to design the files that will be shared. If you can have a common back-end that will NOT be mapped by every task, but WILL be mapped by more than one task, design and LOCK DOWN THE DESIGN of what goes in that one back-end that will be shared. Also consider that building a text-oriented transaction file might be a preferable option in some cases as a way to minimize your use of the limited space of the .MDB / .ACCDB file. For EVERY COMBINATION of apps that must share data between two isolated apps, BE PREPARED to share them in files that might not be inside of Access. For example, a transaction file that is essentially a .CSV text file. And be prepared to rename or erase or otherwise mark that file as "USED" in some way so that you don't try to import it twice. How you do that specifically has to be up to you.

IF you use a divide-and-conquer method, two things happen. First, though the problem might expand, you CAN isolate the parts that are affected by this hypothetical expansion. Second, while this won't prevent some disruption, it allows continuing use of parts not affected by catastrophic changes. AND it won't nearly as often require MASSIVE changes - because you have already divided the problem into pieces-parts. This is the key, though. You CAN'T do the above without enough planning to identify the overlaps and non-overlaps, which means you have to identify the distinct parts that will get their own dedicated sub-apps. So my original discussion on extensive planning remains in place and I stand behind it.

One last thought: IF you were going to ask the question about a project that cannot be divided into applicability segments, go away because that question if asked regarding a monolithic project is unanswerable even in the abstract.

No the problem has not changed
Perhaps those who read the word 'project' immediately thought (this is because usually the Access developers work on a single file) to a single Access file
But already from post9 it was expressly indicated that a 'project' is to be understood as composed of one or more Access files
"..I asked how to organize a project (also understood as a set of multiple Access files.."

 

[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.

The fact that you worked on a project consisting of a single 70 Mbyte form/report/code file, does not mean that there cannot be projects of larger dimensions

 

[The_Doc_Man]

No the problem has not changed
Perhaps those who read the word 'project' immediately thought (this is because usually the Access developers work on a single file) to a single Access file
But already from post9 it was expressly indicated that a 'project' is to be understood as composed of one or more Access files
"..I asked how to organize a project (also understood as a set of multiple Access files.."

I SPECIFICALLY addressed the "multiple section, larger dimension" issues. If this is not enough, then I repeat that YOU DO NOT APPRECIATE what you are actually asking. I'm trying to be polite, but your continued rejection of what we all have been telling you makes me think that this is becoming an exercise in mental auto-eroticism. (Can't use the exactly correct "M" word here for multiple reasons including censorship and the fact that such a word would negatively affect the forum's online scores.)

And YES the scope has changed as your original request, as clarified in your post #5, asked about exceeding limits by one object.

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 "development environment" for an Access project IS the single file MSACCESS.EXE, which works on one object at a time, so there is an implication of a single object there. You later clarified a little bit, but still left something totally open-ended.

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

OK, I'll answer it this way. No disrespect intended, but to even accept consideration of a project with undefinable growth limits is insane. No decent manager would ever accept such a project. If I owned a company and some idiot employee calling himself a manager accepted such a project while working for me, that person would no longer be working for me AND would be personally on the hook for liability issues originating from or related to having exceeding business authority. The relevant problem is, in the real world, that a project's internal connectivity issues grow either exponentially or factorially vs. the growth of the number of elements, and NEITHER growth style has a shallow slope.

I watched a U.S. Navy project called "DIMHRS" go belly-up, not because it couldn't be designed at a high level, but because it had so many interactions that nobody could agree on how to do the detailed conversions for interactions between any two branches of the U.S. military that, years ago, had each developed their own system. Which they did because there WERE no easy ways to interconnect systems in the 1950s and 1960s. This little project didn't even have the "infinite growth" problem and $10 Billion (U.S. dollars) was not enough. That amount was where Congress "pulled the plug" but I can tell you it would have continued to grow seemingly without limit.

This description ("growth cannot be defined") is EFFECTIVELY an infinite project which will require infinite resources. Don't you SEE that? If you CANNOT place a limit on it, I guarantee in writing that it won't get done to everyone's satisfaction - potentially, to ANYONE'S satisfaction. But you later gave us THIS question.

how to create/organize a project whose size in terms of number of objects (form/report/code) goes beyond the maximum limit possible with Access

When dealing with Access limits, my previous discussion on "divide and conquer" (into separate and distinct targeted apps) means that you HAVE no Access limits as long as you can still subdivide the problem. All the remaining limits are then imposed by the capacity of your machine including CPU capacity, memory size, disk capacity, network capacity, and internal bus limitations. So buy another few machines and keep growing the pieces-parts. Without specifics, I believe that no other answers are possible. And if you cannot further subdivide, you are stopped by a limit that nobody will be able to surpass.

 

[Edgar_]

Yes, the use of export/import procedures for single objects on text file is already foreseen (Tim Abell's Vcs)
But I do not think this thing has any importance in relation to the 3d object

I don't know who Tim Abell is, I'm talking about Access. But think of it this way:
1. Single Access file is near its limit
2. Export/Import objects dedicated to a certain aspect of the application to new file
3. New file has fresh limits
If you're certain you will reach the file limits, then divide and conquer from the beginning.

Now, like lawyers in my ranch say: "Assuming without conceding", that you will build that database. I won’t ask who will learn to use your system with 30,000+ forms and reports, or how much time and effort it takes to build it. Instead, how would you move forward now that you have this information? We’re over 90 posts in, so how would YOU begin? You have a wealth of knowledge in this thread. What do you think is the best course of action now? How do you start mindfully?

 

[gemma-the-husky]

The fact that you worked on a project consisting of a single 70 Mbyte form/report/code file, does not mean that there cannot be projects of larger dimensions

And you academically discussing a notional super sized project doesn't mean you really have one.

I still don't understand why every month you need to redesign the project, unless it's not been well designed in the first place. That's why I would rather not discuss hypotheticals.

 

[amorosik]

And you academically discussing a notional super sized project doesn't mean you really have one.
I still don't understand why every month you need to redesign the project, unless it's not been well designed in the first place. That's why I would rather not discuss hypotheticals.

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

 

[amorosik]

I don't know who Tim Abell is, I'm talking about Access. But think of it this way:
1. Single Access file is near its limit
2. Export/Import objects dedicated to a certain aspect of the application to new file
3. New file has fresh limits
If you're certain you will reach the file limits, then divide and conquer from the beginning.

Now, like lawyers in my ranch say: "Assuming without conceding", that you will build that database. I won’t ask who will learn to use your system with 30,000+ forms and reports, or how much time and effort it takes to build it. Instead, how would you move forward now that you have this information? We’re over 90 posts in, so how would YOU begin? You have a wealth of knowledge in this thread. What do you think is the best course of action now? How do you start mindfully?

It is a developer who has created a procedure that can be found with the name vcs-access and that allows the export of all the objects contained within an Access file to a text file, and the corresponding import to recreate the original project

The procedure for dividing a single-file project into a multi-file project is not so trivial, in addition to the division of forms / reports / code on different files there are many other aspects to consider, for example:
- maintain common code modules or not
- how reconnect forms/reports to data tables in case an updated Access file is provided
- how to monitor the use of available memory
- and others

How do I start?
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
The second step is to understand exactly what is meant by "system resources" because from some tests performed I have not yet been able to find the type/value of Windows memory that would be exhausted and that would cause the program to block
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

 

[amorosik]

I SPECIFICALLY addressed the "multiple section, larger dimension" issues. If this is not enough, then I repeat that YOU DO NOT APPRECIATE what you are actually asking. I'm trying to be polite, but your continued rejection of what we all have been telling you makes me think that this is becoming an exercise in mental auto-eroticism. (Can't use the exactly correct "M" word here for multiple reasons including censorship and the fact that such a word would negatively affect the forum's online scores.)

And YES the scope has changed as your original request, as clarified in your post #5, asked about exceeding limits by one object.



The "development environment" for an Access project IS the single file MSACCESS.EXE, which works on one object at a time, so there is an implication of a single object there. You later clarified a little bit, but still left something totally open-ended.



OK, I'll answer it this way. No disrespect intended, but to even accept consideration of a project with undefinable growth limits is insane. No decent manager would ever accept such a project. If I owned a company and some idiot employee calling himself a manager accepted such a project while working for me, that person would no longer be working for me AND would be personally on the hook for liability issues originating from or related to having exceeding business authority. The relevant problem is, in the real world, that a project's internal connectivity issues grow either exponentially or factorially vs. the growth of the number of elements, and NEITHER growth style has a shallow slope.

I watched a U.S. Navy project called "DIMHRS" go belly-up, not because it couldn't be designed at a high level, but because it had so many interactions that nobody could agree on how to do the detailed conversions for interactions between any two branches of the U.S. military that, years ago, had each developed their own system. Which they did because there WERE no easy ways to interconnect systems in the 1950s and 1960s. This little project didn't even have the "infinite growth" problem and $10 Billion (U.S. dollars) was not enough. That amount was where Congress "pulled the plug" but I can tell you it would have continued to grow seemingly without limit.

This description ("growth cannot be defined") is EFFECTIVELY an infinite project which will require infinite resources. Don't you SEE that? If you CANNOT place a limit on it, I guarantee in writing that it won't get done to everyone's satisfaction - potentially, to ANYONE'S satisfaction. But you later gave us THIS question.



When dealing with Access limits, my previous discussion on "divide and conquer" (into separate and distinct targeted apps) means that you HAVE no Access limits as long as you can still subdivide the problem. All the remaining limits are then imposed by the capacity of your machine including CPU capacity, memory size, disk capacity, network capacity, and internal bus limitations. So buy another few machines and keep growing the pieces-parts. Without specifics, I believe that no other answers are possible. And if you cannot further subdivide, you are stopped by a limit that nobody will be able to surpass.

"..forum's online scores.." What is this thing here? Is there anyone who gives us points?

The scope has not changed
1- Yes, the "development environment" is Access.exe
2- Yes, Access.exe works with a single file at a time
3- And for a single Access file there are maximum limits to the manageable objects
There is no doubt about 1,2,3

And no, "..so there is an implication of a single object there.." because this contrasts with the 2nd and 3rd lines above

And so if I ask "..how do you set things up from the beginning to allow this growth without preventing the correct overall functioning?.." it seems clear that one of the possibilities (there could be other?) is to work on a multi-file PROJECT

From the answers it seems to me that, as already written above, the term I use PROJECT is confused with the SINGLE ACCESS FILE
And more than specifying that it is NOT like that (post9), I don't know what to do

 

[Bennet]

Dear Amorosik,

May I ask what your project is and what it's for? I'm intrigued. Genuinely! Especially about the purpose(s) of the new objects you will be adding continually.

 

[amorosik]

The classic system for business management
Which started with the usual parts dedicated to sales, warehouse, accounting and then is expanding
In order to avoid reaching a critical point, I want to prevent any "explosions of objects"
And therefore I would like to modify the structure of the project making it capable of supporting a growth, which obviously will not be of 30k forms, but could overcome the technical limits imposed by the single Access file

 

[Minty]

At last. An actual description of the mythical Unicorn project.
Absolute worst case you'll possibly need 2 or 3 different front ends, connected to a single proper DBMS.

Only taken the thick end of 100 posts for you to actually write something that made sense.

 

[Bennet]

The classic system for business management
Which started with the usual parts dedicated to sales, warehouse, accounting and then is expanding
In order to avoid reaching a critical point, I want to prevent any "explosions of objects"
And therefore I would like to modify the structure of the project making it capable of supporting a growth, which obviously will not be of 30k forms, but could overcome the technical limits imposed by the single Access file

Sounds like a sensible consideration.

You can probably achieve this with dedicated front ends for specific areas of the business which only contain objects relevant to that area. How far you split it is totally up to you. In my opinion, for all practical purposes, splitting a front end in this way will answer all your concerns about exceeding the limits of Access, and will probably be beneficial in other ways too, even if you never hit those limits.

Hope that helps!

 

[GaP42]

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.

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?