How to make a large project?

[The_Doc_Man]

Access has an absolute limit. I don't recall how many Access files you can have open at once, but there IS a limit. You also have limits on the number of open tables, number of connections, and a few other things. I believe that you WILL hit one of these eventually. Which one you hit first depends on your programming approach, but you WILL hit a limit hard and it WILL stop you.

I would like to understand how to do it with Access (if it is possible)

Because of the published absolute limits and the way you ask this question, it is not possible. MSACCESS.EXE has internal lists (for things like concurrently open tables, connections, etc.) that are fixed in size. You want more than 256 connections on a non-O365 DB? Forget about it. We know this because Access specifications are published telling us these limits. Do you honestly think Microsoft is lying?

Access itself (more specifically, MSACCESS.EXE) has only so much room in it for tables. It is not a dynamically interpreted program. It is machine code that has been compiled and is now fixed in size, ultimately by the large number of 32-bit address pointers that remain even in 64-bit versions of Access. Windows can grow tasks very large, but the implication is that these tasks have ways built in to dynamically expand their working lists. Access says it has hard limits in quantities that suggest it has not undergone a massive 64-bit transformation like Excel and Word did. MS has not expanded Access limits like it did for the other products. Do you not understand that?

Your repeated insistence on how to do what Microsoft says can't be done is frustrating to us because it betrays a certain arrogance on your part. Why do you want to ask an unanswerable question? Do you think we are great Zen masters who have an inside track on the unknowable? Are you trying to confound people who volunteer to answer questions free of charge to try to show that we have limits? (We already knew we did.) I am trying very hard to maintain a professional attitude but this is a thread that has developed in a way that leads me to suggest that you don't appreciate the nature of your own question. Your repeated insistence on exceeding limits is exceeding the limits of our patience. If you have a specific technical question, ask here. If you want to know the unknowable, I suggest you make an appointment with the Dalai Lama.

 

[Edgar_]

With 'other tools' it is already clear how to create projects of any size
I would like to understand how to do it with Access (if it is possible)

It's is the same with all the tools, and everything in general.

For example, if I'm going to design a dam, the first thing I need to ask myself is why I need a dam. That's the first step, not the second, and I need the dam because I need water, that's the main purpose. And some people might think the dam is good because it will bring jobs to the area, but building a dam just for the sake of jobs can not be a justification. There has to be a clear need, and that is a need for water. Then, you can determine what kind of solution you're going to design. A dam might not be the most suitable solution; perhaps the best option is to relocate the population to an area with more water. Another possibility could be to build a channel or piping system that transports water from one location to another. Of course, maybe the dam is indeed the solution. But then, what type of dam should you build? Is it a massive concrete wall, or would an earth berm be more appropriate? Perhaps introducing beavers into the ecosystem would be the best approach? But now, there's another constraint: will the topographical configuration allow it? Do we really have decent water runoff to build a containment structure that will allow me to form the basin? Do we have enough information to obtain that answer? Then, do we have the budget to carry out the project? What is the cost-benefit?

The same principle is true for your applications. What kind of software are you going to build? What is it supposed to help with? What is the return of investment expected to be? Only then, can you really establish the type of solution. If you've already decided that you're gonna do that with Access, well, then ask yourself whether Access can do the thing.

Let's suppose you want an image generation application. After research, you figure out you must do heavy array calculations. Good luck with that, Access is very limited for that, even if you have a strong machine. It can, however, help you build a UI that communicates with another system that will carry out the actual calculations. For that, you would first need to determine all of the things the UI must be able to do to be a good option. For example, if it's image generation, you would first need to figure out what controls can handle images. Luckily, there is a control for that, but what if you want to be able to zoom, rotate, draw on it, etc.? Now you have found a problem. You can not use the image control. Luckily, we have the browser control, you can do all of that with it. The next step is researching the steps to make that happen, the amount of code necessary, your time constraints, etc. If you find you have everything necessary and Access will be a good solution, then you can continue and build some prototypes and proofs of concept. Once that is done, you can attempt the integrations to make it seamless.

Now, let's suppose you want an ERP system. Access is great for that, you just need to create, read, update and delete table records. But do you need real time data? do you need heavy security? etc.? Then figure out the options. You must first determine what it is going to be used for, is it large because many people will use it? now you have that other constraint. Where is it going to be located? will it be local or it needs to communicate with others? If so, how?

In general:
1. figure out what you really need
2. check the list of known limitations in your platform
3. test what the app can do by default
4. check your references and see what integrations you can do from that
5. Build your prototypes and proofs of concepts
6. Integrate

Now here's the real juice. You're determined to find out how to do the previous steps when the project is large. Well, I already answered that in post #15, I gave you two links: Access limitations and and a link to an easy to digest website with hundreds of examples of good practices for a wide range of needs that will help you with a large project. You surely will find the best design pattern for your software. I also wrote a list of considerations for the Access limitations.

Real world example:
When your project is large, it means there will be gaps in the development and you must plan for those gaps. The best example I can give you is that of ChatGPT, because it's very recent, besides, those guys are backed by a lot of money, resources and computer scientists. So, they first built a product, their goal was to make it available publicly through an application.

Release updates:
Their first release was just an input box where you could write a prompt and a div for the response to that prompt. Then, they added some security layer to limit the amount of people who could use the service. Then, they introduced formatting capabilities for the responses. Then, they had to think of ways to limit the type of responses and prompts that can be responded. Then, they added a paid-for version. Then, they added a more capable model. Then, they introduced plugins and customizations.

Those are the milestones I recall. You have to plan your own software milestones as well. Once you establish a release plan, you can identify the necessary features and functionalities for both your backend and frontend that align with your upcoming release and set achievable goals. As you near your target, you might want to expand its scope. In such cases, design patterns can be invaluable, as they are tested in battle blueprints that work for those cases. You can explore a variety of design patterns in the link I provided.

TL;DR
It depends.

 

[amorosik]

Access has an absolute limit. I don't recall how many Access files you can have open at once, but there IS a limit. You also have limits on the number of open tables, number of connections, and a few other things. I believe that you WILL hit one of these eventually. Which one you hit first depends on your programming approach, but you WILL hit a limit hard and it WILL stop you.

Because of the published absolute limits and the way you ask this question, it is not possible. MSACCESS.EXE has internal lists (for things like concurrently open tables, connections, etc.) that are fixed in size. You want more than 256 connections on a non-O365 DB? Forget about it. We know this because Access specifications are published telling us these limits. Do you honestly think Microsoft is lying?

Access itself (more specifically, MSACCESS.EXE) has only so much room in it for tables. It is not a dynamically interpreted program. It is machine code that has been compiled and is now fixed in size, ultimately by the large number of 32-bit address pointers that remain even in 64-bit versions of Access. Windows can grow tasks very large, but the implication is that these tasks have ways built in to dynamically expand their working lists. Access says it has hard limits in quantities that suggest it has not undergone a massive 64-bit transformation like Excel and Word did. MS has not expanded Access limits like it did for the other products. Do you not understand that?

Your repeated insistence on how to do what Microsoft says can't be done is frustrating to us because it betrays a certain arrogance on your part. Why do you want to ask an unanswerable question? Do you think we are great Zen masters who have an inside track on the unknowable? Are you trying to confound people who volunteer to answer questions free of charge to try to show that we have limits? (We already knew we did.) I am trying very hard to maintain a professional attitude but this is a thread that has developed in a way that leads me to suggest that you don't appreciate the nature of your own question. Your repeated insistence on exceeding limits is exceeding the limits of our patience. If you have a specific technical question, ask here. If you want to know the unknowable, I suggest you make an appointment with the Dalai Lama.

If a single Access file has technical limitations, then we use multiple Access files
It can be a way to overcome the limitations imposed on the single file
If a single Access file can 'hold' a maximum of 1000 forms then perhaps two Access files can 'hold' a maximum of 2000 forms and so on
What could be the cons of using a project of this type?

 

[amorosik]

It's is the same with all the tools, and everything in general.

For example, if I'm going to design a dam, the first thing I need to ask myself is why I need a dam. That's the first step, not the second, and I need the dam because I need water, that's the main purpose. And some people might think the dam is good because it will bring jobs to the area, but building a dam just for the sake of jobs can not be a justification. There has to be a clear need, and that is a need for water. Then, you can determine what kind of solution you're going to design. A dam might not be the most suitable solution; perhaps the best option is to relocate the population to an area with more water. Another possibility could be to build a channel or piping system that transports water from one location to another. Of course, maybe the dam is indeed the solution. But then, what type of dam should you build? Is it a massive concrete wall, or would an earth berm be more appropriate? Perhaps introducing beavers into the ecosystem would be the best approach? But now, there's another constraint: will the topographical configuration allow it? Do we really have decent water runoff to build a containment structure that will allow me to form the basin? Do we have enough information to obtain that answer? Then, do we have the budget to carry out the project? What is the cost-benefit?

The same principle is true for your applications. What kind of software are you going to build? What is it supposed to help with? What is the return of investment expected to be? Only then, can you really establish the type of solution. If you've already decided that you're gonna do that with Access, well, then ask yourself whether Access can do the thing.

Let's suppose you want an image generation application. After research, you figure out you must do heavy array calculations. Good luck with that, Access is very limited for that, even if you have a strong machine. It can, however, help you build a UI that communicates with another system that will carry out the actual calculations. For that, you would first need to determine all of the things the UI must be able to do to be a good option. For example, if it's image generation, you would first need to figure out what controls can handle images. Luckily, there is a control for that, but what if you want to be able to zoom, rotate, draw on it, etc.? Now you have found a problem. You can not use the image control. Luckily, we have the browser control, you can do all of that with it. The next step is researching the steps to make that happen, the amount of code necessary, your time constraints, etc. If you find you have everything necessary and Access will be a good solution, then you can continue and build some prototypes and proofs of concept. Once that is done, you can attempt the integrations to make it seamless.

Now, let's suppose you want an ERP system. Access is great for that, you just need to create, read, update and delete table records. But do you need real time data? do you need heavy security? etc.? Then figure out the options. You must first determine what it is going to be used for, is it large because many people will use it? now you have that other constraint. Where is it going to be located? will it be local or it needs to communicate with others? If so, how?

In general:
1. figure out what you really need
2. check the list of known limitations in your platform
3. test what the app can do by default
4. check your references and see what integrations you can do from that
5. Build your prototypes and proofs of concepts
6. Integrate

Now here's the real juice. You're determined to find out how to do the previous steps when the project is large. Well, I already answered that in post #15, I gave you two links: Access limitations and and a link to an easy to digest website with hundreds of examples of good practices for a wide range of needs that will help you with a large project. You surely will find the best design pattern for your software. I also wrote a list of considerations for the Access limitations.

Real world example:
When your project is large, it means there will be gaps in the development and you must plan for those gaps. The best example I can give you is that of ChatGPT, because it's very recent, besides, those guys are backed by a lot of money, resources and computer scientists. So, they first built a product, their goal was to make it available publicly through an application.

Release updates:
Their first release was just an input box where you could write a prompt and a div for the response to that prompt. Then, they added some security layer to limit the amount of people who could use the service. Then, they introduced formatting capabilities for the responses. Then, they had to think of ways to limit the type of responses and prompts that can be responded. Then, they added a paid-for version. Then, they added a more capable model. Then, they introduced plugins and customizations.

Those are the milestones I recall. You have to plan your own software milestones as well. Once you establish a release plan, you can identify the necessary features and functionalities for both your backend and frontend that align with your upcoming release and set achievable goals. As you near your target, you might want to expand its scope. In such cases, design patterns can be invaluable, as they are tested in battle blueprints that work for those cases. You can explore a variety of design patterns in the link I provided.

TL;DR
It depends.

No, actually when you work with Microsoft Access, you USUALLY work with a single file that contains everything
The same thing is not true with all the other development environments, just look at a Delphi or Visual Studio project or a Php or Android Studio or RubyOnRail project
They are always projects composed of a series of files that are then joined together by the compilation phase to obtain the final executable or a sequence of many executables that are launched only when necessary
This way of working is not common with the Access environment, which in itself imposes limitations on the number of possible objects, limitations that are quite broad but against which surely more than someone has crashed
From these considerations my initial question starts: how to organize a project in order to overcome the limitations of the single Access file
This way of working allows you to work on projects of any complexity, obviously always respecting the need to observe the rules of good programming

 

[Edgar_]

I'm sorry, what is this?

How about this?

And this?

Or probably this?

Got anything to say about this?

And I'm not even done.

 

[Pat Hartman]

"..how do you 'set things' up from the beginning to allow this growth.."
"..how to organize 'a project' to avoid it reaching dimensions incompatible.."

1. If your scope is infinite, don't start developing on a platform with limitations. Access has an easy path to upsize by moving from ACE to SQL Server. That pretty much expands your database size to whatever SQL Server can support and your concurrent users to whatever SQL Server can support. Pretty big numbers. If you think you will want to upsize, then do not develop using the methods power users use when developing with ACE. Start by assuming the BE is SQL server and develop using that mindset. I can convert any application I built myself from ACE to SQL Server in an afternoon or however long it is going to take to thoroughly test all functions.
2. Unless you are the owner of the project and are paying for it, the scope is not in your control. Apparently you think that you get to decide when enough is enough. You don't.

 

[CJ_London]

They are always projects composed of a series of files that are then joined together by the compilation phase to obtain the final executable or a sequence of many executables that are launched only when necessary
This way of working is not common with the Access environment

Sounds like you are talking about library files - already mentioned at least twice in the previous posts

 

[amorosik]

1. If your scope is infinite, don't start developing on a platform with limitations. Access has an easy path to upsize by moving from ACE to SQL Server. That pretty much expands your database size to whatever SQL Server can support and your concurrent users to whatever SQL Server can support. Pretty big numbers. If you think you will want to upsize, then do not develop using the methods power users use when developing with ACE. Start by assuming the BE is SQL server and develop using that mindset. I can convert any application I built myself from ACE to SQL Server in an afternoon or however long it is going to take to thoroughly test all functions.
2. Unless you are the owner of the project and are paying for it, the scope is not in your control. Apparently you think that you get to decide when enough is enough. You don't.

1- I'm not STARTING development
I'm trying to figure out how to evolve projects that are approaching the technical limits of a single Access file

2- Project ownership or who pay for it doesn't matter on the technical issue

 

[amorosik]

Sounds like you are talking about library files - already mentioned at least twice in the previous posts

I'm not talking about library files
I'm talking about any project that can be built with most other code development environments
Where exactly did you read that I would suggest using library files?

 

[amorosik]

I'm sorry, what is this?
View attachment 116525

How about this?
View attachment 116526

And this?
View attachment 116527

Or probably this?
View attachment 116528

Got anything to say about this?
View attachment 116529

And I'm not even done.

I don't understand what you mean
If you explain me i would be grateful

 

[Edgar_]

I don't understand what you mean
If you explain me i would be grateful

Those screenshots show ways to use external sources, like:
The references window grants access to external libraries.
The external data ribbon tab offers data extraction methods.
The code snippet shows an HTTP request for external sources.
Controls have path fields to specify external sources instead of embedding.
The database split function separates tables and UI

 

[The_Doc_Man]

I'm trying to figure out how to evolve projects that are approaching the technical limits of a single Access file

The problem with this question is that if your "single project" has to use multiple files - which I am not saying it cannot... - you STILL run into the fact that you are using ONE file - MSACCESS.EXE - to drive it all. There is a concept in Windows that relates to how many system resources a given task can consume. Go ahead and link to 32 different database app files in the same task - but they are ALL linked through Workspace(0), which is in turn based on whatever memory limits are built into the executable image. Access, given enough tables and active forms, will reach 2 GB of virtual memory and will die. And then you are done. It might be a "stack overflow." It might be a "stack collided with heap" error. It might be a "heap full" error. But Access will die when you try to cram 10 pounds of in a 5-pound sack.

You could, in theory, launch a second app on the same machine in a separate task, giving you another 32 files and another 2 GB of task virtual space, but there is this little "gotcha" called "process independence" that would prevent the two tasks from easily communicating with each other. You see, Access is built to respect the Windows security feature that prevents task A from interfering / interacting with task B. Their memories are separate and distinct. You can't cross the task boundary except via some complex I/O mechanism that will emulate a herd of frigid turtles (who, being amphibians, are slowed down by the cold...).

I'm not trying to trivialize your questions, but you don't seem to understand the very nature of what you are asking. It is the old conundrum of the immovable object vs. the irresistible force. You have asked for infinities when Windows is based on finitudes (yes, it's a valid word) constraining your actions so that your task can coexist with other tasks trying to occupy the same memory space. This memory allocation to distribute isolated resources among multiple tasks is a very basic feature of advanced time-sharing operating systems, whether we are talking time-slicing or multi-threaded schemes. Even MS-DOS could time-slice - 40+ years ago. Memory allocation / management is a foundation of task security in that you have limits in what you can touch and how you touch it.

What do you do when you run into a finitude and have no way to work around it? Regret your choices because you screwed up your design. Then you go back and try to retrofit yet another patch on the blown-out bicycle tire. But first you have to find the hole that needs the patch, and as you do this often enough, it becomes increasingly harder.

 

[CJ_London]

Where exactly did you read that I would suggest using library files?

I'm referring to the other responders - clearly you do not understand what they are telling you

I'm not talking about library files
I'm talking about any project that can be built with most other code development environments

In access they are called libraries - or add-ins to add into access for any application, not just a specific application. That is the way you might include something written in say C# into an access application.

Your question is hypothetical - and you have not clarified if you are talking specifically about access. So I'm now dropping out, I have better things to do, like helping people who want real help

 

[amorosik]

Those screenshots show ways to use external sources, like:
The references window grants access to external libraries.

Yes, using external libraries is a possibilityIf the technical limitations of a single Access file can be increased by adopting external libraries then this could be an avenue to experiment with

The external data ribbon tab offers data extraction methods.

The ribbon indicates the possibility of connecting data from an external db, and this is already the case
Keeping data inside an Access file is a possibility not contemplated

The code snippet shows an HTTP request for external sources.

I did not understand this, we are talking about increasing the number of forms/reports/code manageable on a project, access to an http server has nothing to do with it

Controls have path fields to specify external sources instead of embedding.

Even the images, obviously, are kept on disk and loaded dynamically, already now

The database split function separates tables and UI

Yes, we are talking exclusively about forms/reports/code, the data is kept by an external database

 

[amorosik]

Your question is hypothetical - and you have not clarified if you are talking specifically about access

"..get close to the limits of the Access environment.." post1
"..also understood as a set of multiple Access files.." post9
"..development environment, in this case Access,.." post18
"..various versions of Access are.." post23
"..that is normally never used with Access.." post33
"..that uses Access as a development environment.." post37 (expressly in response to CJ_London)
and many other

Yes you're right, maybe not It's clear I was talking specifically about access

 

[Edgar_]

After 50+ posts, I suggest making another thread asking something along the lines of:
> How to build a scalable frontend solution for continuous development in Access?
> How to optimize a frontend solution to avoid being stopped by the limits of Access?

I did not understand this, we are talking about increasing the number of forms/reports/code manageable on a project, access to an http server has nothing to do with it

Having your data in an external source will impact your frontend tremendously, it has A LOT to do with forms/reports/code manageable

And you can have multiple frontend files connected to the same data sources.

 

[The_Doc_Man]

Ah, MajP, you have reminded me of that great old Woody Allen movie, What's Up, Tiger Lily - in which the protagonist remarks "I would call you a sadistic sodomistic necrophile - but that would be beating a dead horse."

 

[amorosik]

After 50+ posts, I suggest making another thread asking something along the lines of:
> How to build a scalable frontend solution for continuous development in Access?

I think I asked this in this thread, what would be the point of opening another one?

> How to optimize a frontend solution to avoid being stopped by the limits of Access?

Having your data in an external source will impact your frontend tremendously, it has A LOT to do with forms/reports/code manageable

I have already written several times that the data is in an external db server
And therefore this consideration has NO relevance for the solution to the problem posed

And you can have multiple frontend files connected to the same data sources.

Yes, this is a viable path, if a single Access file can contain at least X objects then using multiple Access files could help achieve the desired goal
But seeing what @The_Doc_Man writes, even this possibility has limitations in the sense that only a part of the memory provided by the operating system can be used among all the active Access instances

 

[amorosik]

Is this thread for real or is everyone being trolled? If this was April 1st then i would get the joke. I credit everyone for their persistence and giving a wealth of good information, but this has to be the dumbest thread I have read on this forum. It is a bunch of hypothetical impossibility. Then it is like trying to explain to a five year old that keeps saying "yeah, but what about..."
View attachment 116530

It seems like a Woody Allen joke, which notoriously doesn't make anyone laugh
It's strange how administrators allow posts offensive of this type
But maybe they haven't seen it yet

 

[Edgar_]

what would be the point of opening another one?

Basically, your thread got derailed, you've been told the topic was not clear and you still have not found a solution. I doubt anything here is a solution to what you need. We're not even sure you know what you need.

If what you need is the number of forms/reports that can be created, or how many objects in a form/report, then why don't you just create them programmatically? You could even let us know your findings and you would be able to get precise strategies to avoid having problems.

Try this for forms:

Sub createforms()
    On Error GoTo errh
    Application.Echo False
    Dim i As Long
    For i = 1 To 1000000
        With Application.CreateForm()
            DoCmd.Close acForm, .Name, acSaveYes
            Debug.Print i
        End With
        DoEvents
    Next i
    Application.Echo True

errh:
    Application.Echo True
End Sub

No idea how much time it would take, though, I just tested it and it's roughly one new form every second, maybe copying them in bulk is quicker