How to make a large project?

[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

 

[Cotswold]

@DakotaRidge
When I supplied software I would take the Microsoft advice of database sizes as maximums and not as a challenge to prove them wrong. It isn't a competition.
Yes, sometimes a customer ran databases oversize but I always advised against and refuse to support it. I regarded fixing corrupted databases as a waste of my time and the larger they are then the possibility increases. In many businesses current data is often small and once processed rarely accessed again. So why have many years of the stuff pointlessly sloshing about all the time? As far as the FE is concerned, smaller the better in my opinion. Why drag a huge FE up and down the wires? Even the FE bloats over time and needs to be replaced.

 

[The_Doc_Man]

So why have many years of the stuff pointlessly sloshing about all the time? As far as the FE is concerned, smaller the better in my opinion. Why drag a huge FE up and down the wires? Even the FE bloats over time and needs to be replaced.

To amplify Cotswold's point, even with its anal retentive approach to archiving, the federal government DOES trim databases regularly. In my biggest personal database, we had a minimum retention of six months but tempered by the fact that if anything in our DB was not yet completed, we couldn't archive it. We had a rule that we would NEVER mark something for archiving until every option was in a completed status AND it was at least six months old. THEN and ONLY THEN, we could archive it to an external file, followed by deleting it in the master file, which would then undergo a compact & repair.

But "six months" was the need of that particular database. For the U.S. Naval Reserve personnel DB, transactions had to stick around online for research for 10 years as a matter of federal law dealing with military personnel records. Again, archiving DID occur, but in this case, the applicable regulations said 10 years. After 10 years, a different federal division took over and became the primary keeper of the archived files. You have to know the requirements in order to know when to get rid of the "stuff pointlessly sloshing around" - and the odds are that there WILL be some limit somewhere. It pays to know your limits.

 

[Isaac]

I used to work for the Document Management dept of Wells Fargo Auto Finance. Very interesting subject doc retention, because there is a push-and-pull back and forth between two competing interests:
- Retain important docs as long as possible to cover your a***
- Delete docs as soon as possible also to cover your a*** in case of a hostile request - subpeona, discovery, lawsuit, etc

It's almost like you do and don't want to retain documents at the same time. We also used Iron Mountain, a relatively expensive way to retain paper - and yes it's actually in a mountain/mine, which seemed to surprise Musk and Trump (somewhat humorously so, I thought, as it's a standard vendor for many corporations to use).

My six sigma greenbelt project was about electronic imaging, which in 2007 Wells was just beginning to take fuller advantage of. (As usual, 20 yrs behind everyone else - but they're a very cautious bunch)