How to make a large project? (3 Viewers)

[amorosik]

It's only of academic interest ...

Even if it were just an academic interest, the topic would still be of great value to someone who uses Access
Even a linked list or a bubble sort, are purely academic interests (unless one builds a db server or similar), but are still of great interest to understand how things work in certain fields

 

[CJ_London]

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

That was wat I said way back when.

 

[Pat Hartman]

Even if it were just an academic interest, the topic would still be of great value to someone who uses Access

This particular thread is useless since it is not based on reality. Access is infinitely expandable given that you can always make multiple FE's and have them link to each other and you can link to multiple BE's. The central advice you have been given is that YOU need to keep the project organized as it grows. The hard limit of Access is that it doesn't do web stuff efficiently so it is a desktop application or runs through Citrix or RD. The other limit is that without source control, it is very difficult to have multiple simultaneous developers for a single FE.

 

[Edgar_]

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

Testing has been mentioned multiple times. Here's what ISTQB, an authority in the subject, says about that:

1. Testing shows the presence, not the absence of defects. Testing can show that defects are present in the test object, but cannot prove that there are no defects (Buxton 1970). Testing reduces the probability of defects remaining undiscovered in the test object, but even if no defects are found, testing cannot prove test object correctness.
2. Exhaustive testing is impossible. Testing everything is not feasible except in trivial cases (Manna 1978). Rather than attempting to test exhaustively, test techniques (see chapter 4), test case prioritization (see section 5.1.5), and risk-based testing (see section 5.2), should be used to focus test efforts.
3. Early testing saves time and money. Defects that are removed early in the process will not cause subsequent defects in derived work products. The cost of quality will be reduced since fewer failures will occur later in the SDLC (Boehm 1981). To find defects early, both static testing (see chapter 3) and dynamic testing (see chapter 4) should be started as early as possible.
4. Defects cluster together. A small number of system components usually contain most of the defects discovered or are responsible for most of the operational failures (Enders 1975). This phenomenon is an illustration of the Pareto principle. Predicted defect clusters, and actual defect clusters observed during testing or in operation, are an important input for risk-based testing (see section 5.2).
5. Tests wear out. If the same tests are repeated many times, they become increasingly ineffective in detecting new defects (Beizer 1990). To overcome this effect, existing tests and test data may need to be modified, and new tests may need to be written. However, in some cases, repeating the same tests can have a beneficial outcome, e.g., in automated regression testing (see section 2.2.3).
6. Testing is context dependent. There is no single universally applicable approach to testing. Testing is done differently in different contexts (Kaner 2011).
7. Absence-of-defects fallacy. It is a fallacy (i.e., a misconception) to expect that software verification will ensure the success of a system. Thoroughly testing all the specified requirements and fixing all the defects found could still produce a system that does not fulfill the users’ needs and expectations, that does not help in achieving the customer’s business goals, and that is inferior compared to other competing systems. In addition to verification, validation should also be carried out (Boehm 1981).

 

[The_Doc_Man]

@Edgar_ ... I see you are quoting Barry Boehm among your other sources. I used to have a couple of his books on project management. Sadly, I lost some of those excellent reference works in 2005 in the aftermath of Hurricane Katrina's flooding. However, I remember that Boehm had a LOT of good things to say about testing as well as on pre-planning and about careful selection of project members who were familiar with the project's environment - as opposed to dragging someone "off the street" and expecting rapid productivity. He was big on "learning curves" as well as testing, as I recall.

 

[gemma-the-husky]

Testing has been mentioned multiple times. Here's what ISTQB, an authority in the subject, says about that:

1. Testing shows the presence, not the absence of defects. Testing can show that defects are present in the test object, but cannot prove that there are no defects (Buxton 1970). Testing reduces the probability of defects remaining undiscovered in the test object, but even if no defects are found, testing cannot prove test object correctness.
2. Exhaustive testing is impossible. Testing everything is not feasible except in trivial cases (Manna 1978). Rather than attempting to test exhaustively, test techniques (see chapter 4), test case prioritization (see section 5.1.5), and risk-based testing (see section 5.2), should be used to focus test efforts.
3. Early testing saves time and money. Defects that are removed early in the process will not cause subsequent defects in derived work products. The cost of quality will be reduced since fewer failures will occur later in the SDLC (Boehm 1981). To find defects early, both static testing (see chapter 3) and dynamic testing (see chapter 4) should be started as early as possible.
4. Defects cluster together. A small number of system components usually contain most of the defects discovered or are responsible for most of the operational failures (Enders 1975). This phenomenon is an illustration of the Pareto principle. Predicted defect clusters, and actual defect clusters observed during testing or in operation, are an important input for risk-based testing (see section 5.2).
5. Tests wear out. If the same tests are repeated many times, they become increasingly ineffective in detecting new defects (Beizer 1990). To overcome this effect, existing tests and test data may need to be modified, and new tests may need to be written. However, in some cases, repeating the same tests can have a beneficial outcome, e.g., in automated regression testing (see section 2.2.3).
6. Testing is context dependent. There is no single universally applicable approach to testing. Testing is done differently in different contexts (Kaner 2011).
7. Absence-of-defects fallacy. It is a fallacy (i.e., a misconception) to expect that software verification will ensure the success of a system. Thoroughly testing all the specified requirements and fixing all the defects found could still produce a system that does not fulfill the users’ needs and expectations, that does not help in achieving the customer’s business goals, and that is inferior compared to other competing systems. In addition to verification, validation should also be carried out (Boehm 1981).

No 7. Is what I said. The design commissioners and specifiers may not understand the business needs well enough, and may not understand the development possibilities that are available to produce an optimal (or tending to optimal) solution. The developers may not understand the real requirements.

 

[gemma-the-husky]

Even if it were just an academic interest, the topic would still be of great value to someone who uses Access
Even a linked list or a bubble sort, are purely academic interests (unless one builds a db server or similar), but are still of great interest to understand how things work in certain fields

But this is completely different to choosing an inefficient sorting algorithm.

I don't believe it is of any use to consider the question "what will we do if the problem is too complicated for MS Access". We are doing that continually in a microcosm. Occasional we get queries that are too large to be processed, and we find a way round it empirically.


You seem to be insistent upon arguing about the difference between 2 infinities as it were. I have never had a time when Access has said "this project is now too big". If that happens I will deal with it, probably by dividing the project into two or more parts. Until that happens it's academic, and not really interesting.

 

[Pat Hartman]

Until that happens it's academic, and not really interesting.

And every solution will be different.

 

[The_Doc_Man]

@amorosik - This post is a serious request to review what level of help you have received to date, based on nearly 150 posts on a technical question.

From this forum, you have gotten answers from:

people with an aggregate of at least 200 years of programming experience, probably more than 300 years worth
people with public sector project experience, government project experience, and private business experience
people who hold advanced degrees and with specialty certificates in various topics
people who have started and maintained their own personal businesses
people who have held government security clearances, implying their level of expertise in their field.

But your responses suggest that the above level of resources was inadequate for your needs.

You have continually claimed that you should be able to get specific answers without defining the specifics of the problem. We have disagreed with you and explained why we did so. Members of this forum have tried to help you with advice commensurate with your description of your question. You have continually responded to our answers with a "yes, but..." approach, signifying that you find our advice inadequate, or that you believe we must have forgotten something. While you deny it, MANY of us feel as though your responses are evasive.

Given the kind of diversity and level of experience of our responding members, do you perhaps ever so faintly begin to think that MAYBE the problem is within YOU? Do you think that MAYBE you really don't understand the level of question you are asking? This is not meant to be taken as an argumentum ad authoritatem discussion nor an argumentum ad hominem attack on you.

Please explain why the responses you got so far are not good enough for you. If this forum cannot answer your questions then the only other way we can help you is to direct you to resources you will need to get those answers... if we ourselves know the answers.

 

[The_Doc_Man]

As an aside, do you recall making this post?

What was/is your "largest" access project

After 100 posts there is still someone who insists on recommending the division between user interface and data Perhaps it is not clear to you that the OP has probably already evaluated the matter and has decided, for the reasons he considers important, to maintain the current architecture...

www.access-programmers.co.uk

Did this comment provide a harbinger of your project question?

What was/is your "largest" access project

After 100 posts there is still someone who insists on recommending the division between user interface and data Perhaps it is not clear to you that the OP has probably already evaluated the matter and has decided, for the reasons he considers important, to maintain the current architecture...

www.access-programmers.co.uk

Our answer to you is simple. You cannot tell WHICH limit you will hit, but when you hit one you will know you have screwed up.

 

[DakotaRidge]

I came across this thread this afternoon. I have one easy question for folks.

Where did the statement that an Accdb file cannot have more than 1,000 forms come from? Is this what MS says?

I created one .Accdb file with more than 4,000 forms. I am working on another Accdb file now that has more than 450 forms, and I am far from being finished with it.

Is there any reason why I was able to exceed the 1,000 mark? Is it because I use very little code? Is it because I didn't split my databases? Is it because my images are pasted into forms and reports? I have hundreds of images in my current project. I am just curious.

Your replies will help me plan and develop my databases.

Thanks.

 

[Josef P.]

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

=> 1000 modules (including forms and reports that have the HasModule property set to True)

 

[DakotaRidge]

Thanks, Josef, perhaps that's why. I don't set the HasModule property because I don't use class modules. I attended a couple of UG presentations in the past two years about classes, but I didn't understand them. More to learn.

 

[isladogs]

@DakotaRidge

Is there any reason why I was able to exceed the 1,000 mark? Is it because I use very little code? Is it because I didn't split my databases? Is it because my images are pasted into forms and reports? I have hundreds of images in my current project. I am just curious.

The specified limit of 1000 code modules is one of several that should be treated as a sensible guideline rather than a hard limit.
In fact the actual limit is 5450, but performance will become abysmal long before reaching that limit.

See my article for more details on this and other limits:

Access Specification issues

Microsoft publishes a list of Access specifications and limits. Most of the information is correct. This article discusses some specification items that are misleading, out-of-date or inaccurate

www.isladogs.co.uk

As for your other questions:
Is it because you use very little code? No
Is it because you didn't split my databases? No
Is it because your images are pasted into forms and reports? No

And before you ask
Is it because you use MVFs? No

 

[The_Doc_Man]

I came across this thread this afternoon. I have one easy question for folks.

Where did the statement that an Accdb file cannot have more than 1,000 forms come from? Is this what MS says?

I created one .Accdb file with more than 4,000 forms. I am working on another Accdb file now that has more than 450 forms, and I am far from being finished with it.

Is there any reason why I was able to exceed the 1,000 mark? Is it because I use very little code? Is it because I didn't split my databases? Is it because my images are pasted into forms and reports? I have hundreds of images in my current project. I am just curious.

Your replies will help me plan and develop my databases.

Thanks.

Historically, earlier versions of Access had more stringent limits, but as new versions came out, those limits got expanded. The original belief in a limit of 1000 forms comes from limits on those historical versions. Now, the limits originate from other considerations.

As a further explanation, Access is NOT an OpenSource product. We cannot see how much space has been pre-allocated for each type of object. We know that Access has physical memory limits on some things. For instance, it used to be smaller, but at this time Access limits table-based and query-based recordsets to 1 Gbyte. The limit on the number of modules (which doesn't necessarily matter to you since you don't use VBA) is harder to pin down, but it comes back to one basic fact: Your .ACCDB file address space is only so big. When you fill it up with stuff, you are done, regardless of whether you are or aren't using supplemental external storage options such as multiple .ACCDB back-ends or other ways to extend the capacity. When the Access front end is clogged up, that's the end of the game.

This stems from choices made literally decades ago on storing various things. For example, you can only have 22.75 inches width on sections in forms and reports because MS chose a WORD-sized coordinate system for visible things. Since coordinates on forms are in TWIPS (=1440 per inch), and you have a limit of 32767 for a WORD integer, you do the math. You are limited to a form or report section being 22.75 x 22.75 inches. You probably won't do much to manipulate those sizes, but those are limits that you will run into even when just building forms & reports using the mouse cursor for the layout. This is one isolated example if why Access has limits. Just for the purists, we have seen an article that suggests one of the next versions of Access will change this limit as well. But the point as an illustration is still valid.

 

[DakotaRidge]

Historically, earlier versions of Access had more stringent limits, but as new versions came out, those limits got expanded. The original belief in a limit of 1000 forms comes from limits on those historical versions. Now, the limits originate from other considerations.

As a further explanation, Access is NOT an OpenSource product. We cannot see how much space has been pre-allocated for each type of object. We know that Access has physical memory limits on some things. For instance, it used to be smaller, but at this time Access limits table-based and query-based recordsets to 1 Gbyte. The limit on the number of modules (which doesn't necessarily matter to you since you don't use VBA) is harder to pin down, but it comes back to one basic fact: Your .ACCDB file address space is only so big. When you fill it up with stuff, you are done, regardless of whether you are or aren't using supplemental external storage options such as multiple .ACCDB back-ends or other ways to extend the capacity. When the Access front end is clogged up, that's the end of the game.

This stems from choices made literally decades ago on storing various things. For example, you can only have 22.75 inches width on sections in forms and reports because MS chose a WORD-sized coordinate system for visible things. Since coordinates on forms are in TWIPS (=1440 per inch), and you have a limit of 32767 for a WORD integer, you do the math. You are limited to a form or report section being 22.75 x 22.75 inches. You probably won't do much to manipulate those sizes, but those are limits that you will run into even when just building forms & reports using the mouse cursor for the layout. This is one isolated example if why Access has limits. Just for the purists, we have seen an article that suggests one of the next versions of Access will change this limit as well. But the point as an illustration is still valid.

Thank you, The-Doc_Man. The next time I read that an .Accdb file can only have 1000 forms, I will know that the author is using an ancient version of Access. I hit the 4,000 mark using Access 2019. I am running A365 now on a 64-bit system.

I am one of the people waiting for MS to support large monitors. I have a large monitor in the basement that I haven't touched in almost a year. I would love to be able to view some of my large Access forms/reports completely on screen. If I could, I would increase the number of fields in my tables and let my MVFs run wild. Scrolling right and down is a pain.

I would also love for Access to be able to address more than 2Gb. I hit that limit a couple of years ago.

 

[Cotswold]

Although only slightly related but it brings to mind something I read somewhere years ago.
Which was that functions should be stored in Modules rather than on forms for security reasons. It was claimed being in Modules they cannot be decompiled/hacked from an ACCDE as easily, or at all. (can't remember which) Or probably more accurately at the time when I read it, in an MDE.

Is this fact or fiction?

 

[gemma-the-husky]

@DakotaRidge

Report width can be an issue. You may be able to get around that by generating a spreadsheet instead of a report.

I am concerned that you use MVFs. I have never ever used one. It does not meet standard RDBS design criteria, and cannot be upsized to SQL server. If you really need a MVF you can design the same functionality with normal tables. In fact you can improve on the way the MVF works if you do it yourself. I have done this once ever from memory.

Many times people think they need wide tables with many fields, but the tables they want are incorrectly normalised, and could be designed differently. Because of this form width requirements, as opposed to report width requirements, may well also be a result of table design that could be improved.

For example, if you have products that you make available in 83 different pack sizes, you might want to see a report that shows the 83 pack sizes horizontally, to compare prices for example. That is going to give you display issues, and you need to agree a way around that. It's also going to give you screen layout issues to achieve that on a form, and again you will need to work out a solution/compromise.

@amorosik
If your database expansion is to add additional periods to working tables, then that is definitely a design issue. Front end changes might be a result of changed requirements, but shouldn't be a result of more trading history.

 

[gemma-the-husky]

With Access there is sometimes a real issue that the maximum size of a database is 2Gb. If you reach that the database will become unuseable, and possible unfixable. I have added a startup message in my larger databases to warn users that the size is getting large (system configurable value of 70%, say), to get the clients to do a C&R of the back end, and/or delete/archive older data.

I think this is a more realistic possibility than the front end getting too big, personally.

 

[isladogs]

Although only slightly related but it brings to mind something I read somewhere years ago.
Which was that functions should be stored in Modules rather than on forms for security reasons. It was claimed being in Modules they cannot be decompiled/hacked from an ACCDE as easily, or at all. (can't remember which) Or probably more accurately at the time when I read it, in an MDE.

Is this fact or fiction?

Fact …. but only if
a) it’s an ACCDE file
b) you add the line Option Private Module in the declarations section. This makes all procedures completely invisible but is only allowed in standard/class modules. See my article:

VBE Option Statements

This article outlines the various option statements that can be used at the start of code modules in the visual basic editor (VBE).

www.isladogs.co.uk