What was/is your "largest" access project

The regular backup is wise. The question of file size actually depends on how you do the mapping AND how you do the size estimates. Windows is not always consistent when reporting file sizes because the units make a difference. If that is a split front-end/back-end case and the TOTAL is bigger than 2 Gb but neither component is that big, then you could make it work. The problem is that the first time you actually touch the REAL 2Gb limit is the day that a Compact & Repair stops working.

Given the size and complexity of your project, I sincerely hope you have studied normalization because while I cannot accuse you of anything at all, I will merely say that the sizes you quoted for a native Access database are suspicious.
I use File Explorer to tell me the size of the database.

It has been between 1.93 and 2.1 Gb for years. C&R always brings 2Gb+ back to 1.9 Gb, but I expect that to fail one day so I would like MS to increase the file size to 1 Tb or at least 8 Gb. I could even live with 4 Gb for a few months.

But I don't know what the REAL 2 Gb limit is. Is it 2.2, 2.4, or 3.0? Or is it a number MS uses to discourage folks from building out their applications? I have read some developers say they have hit it, but they didn't say when or what version of Access (I am using Access 2019 as I wait for 2024).

Did MS increase it recently when they increased the number of connections that Access can have? I have been trying to get there for years. I want a new PC, and I would love to see smoke billow from this one.
 
The real limit is exactly 2^31 bytes, or 2,147, 483,648 bytes, which is half of the address space available on a 32-bit address bus. (In fact, because of the memory layout of compiled Windows tasks, Access reserves the high half of the address space for any DB requirements because the low half includes MSACCESS.EXE code, various mapping tables, stack space, heap space, and room for library references.

Access WILL NOT grow to expand their address space to because if they did, Microsoft would have two different products in competition with each other, and the SQL Server competitor EASILY would win any legit competitions with the JET/ACE engine. Access was designed as a small business tool that could transition to become the front end to their high-end SQL product. There is no profit in expanding the address space. It lacks some features that are found in SQL Server precisely because MS wants to avoid the competition in the first place and (gently?) shove you towards their mainstream product.
 
If you need more gb, split your db and use sql server express for the back end
 
My largest database has 4600 forms, 3200 reports, and 4100 tables. A few tables have between 100 and 200 fields. Most of the forms and reports have a custom graphic and many of them have text-to-speech. It is a little over 2 Gb. It doesn't have a lot of data. And there is very little code.

4600 forms, 3200 report, is a real Access file monster
2 Gb for the file, but wich part for data (table) and how many Mbyte for form, report, macro, module?
 
Last edited:
That's impressive. It also must be a nightmare to maintain.

I would like to know if it runs on low-end computers with ease. If not, then maybe separate it across multiple specialized frontends and backends. See if you have repeating data models, those should be normalized. If you have many forms that look the same and the only thing that changes is the graphic, then make your graphics (is it charts?) dynamically appear according to the form's purpose. I can't imagine creating 4600 different forms.

BTW, what do you mean they have text-to-speech? your users need to listen to the app?
Yes, Edgar, the application reads information aloud.

You have to understand that this database has an information focus rather than a data focus. It has lots of information about each entity (e.g., staff member, customer, project, etc.) Each staff member for example has a name(s), multiple titles, security clearances, professional certifications, telephone numbers, grade point averages, and so on. For one personnel statement, the application may only read aloud a person's name, security clearance, and most recently obtained professional certification. This could be information used to qualify a person for a job.

Some of the forms have several command buttons that read information aloud. Each button reads a few sentences, that's all. The next button reads different information depending on where it is located on a form.

Speech in a report introduces the user to that record. For example, "This is Edgar's profile. Edgar is a database guru in the corporate offices." The person's name, title, and location is read from the underlying table. The rest of the sentence is built into the database. After this statement is read, the full report is displayed.

The only nightmare now is separating the domains. I developed the application in such a way, that normal maintenance isn't necessary. I achieve that by using as little code as possible. So usually there is no need for error handling. If a capability isn't absolutely needed, it doesn't get added to the application.

Years ago I ran the database on a low-powered laptop with a Celeron processor. I run it now on a Core i7 8th Gen with 16Gb of RAM. It is designed so a user gets only the domains he needs to do his job. There are about 25 domains.

I can't imagine doing the job with less than a few thousand forms and thousands of reports. There are just too many tasks that are needed at different times and they need to be used in different ways and in different orders depending on the situation. To track tasks, the database has about 20 Kanban boards and a few Scrum boards. When a task is is not done, millions of dollars could be lost. And someone can also go to jail if a mistake is made. This is not the case in every industry but it is in the industries that I target.

Here's a screenshot of a form I created a couple of weeks ago. The idea is to help users see the progress they have made with pricing their contracts. The background graphic was created in PowerPoint and pasted into Access. Colored circles were positioned on the image using a scoring built into the database. The form automatically calculates the total score. The maximum score is 40 points.
 
Last edited:
The real limit is exactly 2^31 bytes, or 2,147, 483,648 bytes, which is half of the address space available on a 32-bit address bus. (In fact, because of the memory layout of compiled Windows tasks, Access reserves the high half of the address space for any DB requirements because the low half includes MSACCESS.EXE code, various mapping tables, stack space, heap space, and room for library references.

Access WILL NOT grow to expand their address space to because if they did, Microsoft would have two different products in competition with each other, and the SQL Server competitor EASILY would win any legit competitions with the JET/ACE engine. Access was designed as a small business tool that could transition to become the front end to their high-end SQL product. There is no profit in expanding the address space. It lacks some features that are found in SQL Server precisely because MS wants to avoid the competition in the first place and (gently?) shove you towards their mainstream product.
Yes, The_Doc_Man.

Clayton Christensen wrote years ago in one of his books about Disruptive Innovation that Access could kill off SQL Server if it was allowed to run wild. Personally I would like for MS to do away with Runtime Access and EOL older versions. They don't make any money off Runtime and older versions prevent them from investing in the people they need to enhance the program.

I always see professional developers complain that nothing new has been added to Access in years while major improvements have been made to Excel. The last major improvements that were made with A2007 when multi-value fields and a few other features were added.

In addition, new products such as PowerApps have been introduced. Many developers still support A2007 and A2010. Their clients paid $100 for Access years ago and now they just pay developers to fix things because the macros in old versions did not have error handling and no MVFs. What does MS get from companies that are still on A2007 and A2010?

Perhaps Access was designed as a small business tool. But I would say that it is also a great small workgroup tool. If my smartphone, which I hate, has gigabytes of memory why can't my Access database have a terabyte? I have no idea how large a SQL Server database can be but does SQL Server allow users to build graphics-rich forms and reports as easily as Access? Do SQL Server databases read information aloud? Does SQL Server play my favorite music?

Here's a screenshot of a form in my database. The idea is to show users the progress they have made in pricing a large contract. Green circles indicate good and red indicates bad. The squares change color using conditional formatting. The scoring uses a five-level Likert scale. The form automatically calculates a total score for each record. The background image was created in PowerPoint.
1691344775571.png
 
4600 forms, 3200 report, is a real Access file monster
2 Gb for the file, but wich part for data (table) and how many Mbyte for form, report, macro, module?
Amorosik.

At this time I have one ACCDB file with all of the tables, queries, forms, and reports in it. One day I may split it into a front end and a back end. I won't do any splits until I have separated the database into multiple files by domain. Currently I am the only user and I learned in the past few days about a security problem with split databases that concerns me.

I really have no interest in managing both a FE and a BE. In my opinion, it would be a waste of my time and effort. I make too many changes to the tables, forms, and reports to waste time on FE and BEs.

And I am working with three Bodies of Knowledge. The project management is the largest BOK and I have a lot more to do with it, particularly with regards to Agile project management.
 
I really have no interest in managing both a FE and a BE. In my opinion, it would be a waste of my time and effort. I make too many changes to the tables, forms, and reports to waste time on FE and BEs.
How would that involve extra work? :(
 
The real limit is exactly 2^31 bytes, or 2,147, 483,648 bytes, which is half of the address space available on a 32-bit address bus. (In fact, because of the memory layout of compiled Windows tasks, Access reserves the high half of the address space for any DB requirements because the low half includes MSACCESS.EXE code, various mapping tables, stack space, heap space, and room for library references.

Access WILL NOT grow to expand their address space to because if they did, Microsoft would have two different products in competition with each other, and the SQL Server competitor EASILY would win any legit competitions with the JET/ACE engine. Access was designed as a small business tool that could transition to become the front end to their high-end SQL product. There is no profit in expanding the address space. It lacks some features that are found in SQL Server precisely because MS wants to avoid the competition in the first place and (gently?) shove you towards their mainstream product.
The_Doc_Man.

You mention 32-bit. I am running my database on a 64-bit laptop. Does that make any difference?

When I commercialize the application I plan to sell it with a PC. I don't want users to have any hardware problems that I don't have.
 
How would that involve extra work? :(
Using a split database requires two files. To me that means twice as much work and managing a split database could also mean using special tools. I don't use and I don't want to use anything other than Access.
 
Using a split database requires two files. To me that means twice as much work and managing a split database could also mean using special tools. I don't use and I don't want to use anything other than Access.
Really?
You amend the tables in one.
You amend the everything else in the other.

All done within Access.
Two files yes, but not twice the work. :(
Same work, just different files.
 
For the benefit of those unfamiliar with David's work (@DenverDb), it is a huge and very idiosyncratic database which contains a lot of features that most developers avoid such as MVFs and TTS. David uses a lot of graphics in his forms and also takes pride in using as little code as possible.

Several of us have spent a lot of time and effort trying to convince David to split the database over the past few years but we go around & around in circles.
David is (wrongly) convinced it is extra work and is VERY unlikely to change his opinion no matter how many of us explain why it would significantly benefit his application, both during development & once it is completed and released.

@DenverDb (David)
You claim that an article by Mike Wolfe described .... "a security problem with split databases" that concerns you

I assume you are referring to this article: Microsoft Access vs. SQLite: Security Comparison (nolongerset.com).
If so, I think you have significantly misunderstood the point of the article. Access can never be 100% secure because it is a file based system.
The security is definitely not weakened by splitting a database. In fact, it becomes slightly more secure - partly by separating the end user from the backend data tables stored on the network
However, the data would be much more secure using a RDBMS such as SQL Server
 
The last major improvements that were made with A2007 when multi-value fields and a few other features were added.
been a few - some less successful such as web apps. Now we have dataverse, recent improvments to graphs (appeals to some, not others), update of the web control to name a few.

These two statements are incompatible
I really have no interest in managing both a FE and a BE. In my opinion, it would be a waste of my time and effort. I make too many changes to the tables, forms, and reports to waste time on FE and BEs.
When I commercialize the application
How do you plan to maintain it? You decide on a necessary improvement, perhaps as a result of upgrades to Access/Office/Windows, are you going to ask each customer to somehow return the single file (circa 2Gb) to you for you to make the change and return it to the customer and in the meantime, they can't use it?

I learned in the past few days about a security problem with split databases that concerns me.
what exactly? - just answered by Colin.
 
Using a split database requires two files. To me that means twice as much work and managing a split database could also mean using special tools. I don't use and I don't want to use anything other than Access.
No special tools, Access does this natively. To split the database, you just do this:
Ribbon > Move Data > Access Database > Split Database
Done.

Your users are going to have problems if all of them use the same file! Oh, but you as the developer will have the biggest problems.

Just give each team the frontend required. If this is for a construction company, for example, you can give a specialized frontend to:
construction team: construction progress, contractors management, material consumption, etc
costing team: budget control, estimates, cost analysis, etc
human resources team: contracts, social security, leaves, payrolls

And all of those teams could point to the same database and not have the same tables available.


You will see no benefit until you have active users working with your app and you need to maintain it while they're working. Trust me, you don't want to wait for disaster, your client probably knows no better, but you should.
 
Last edited:
Colin said it correctly. I have a real problem with the concept of a split database.

I learned from Mike's article that someone in the organization can change the data if he has access to a split database. I don't want that to happen.
In other words, I do not want my database to be used by multiple people. That's not what all other developers want when they develop a database. I want one person to own the database and for that person to generate reports that are distributed as PDFs. That's totally contrary to everything we try to achieve with Access.

I have my reasons for thinking this way. It is mainly about the information being company sensitive and so no one other than the owner should have access to it. In some cases, the data will be classified and in other cases, exposure could result in jail time. These are situations that 99.999% of Access developers never have to deal with. Sometimes PCs used to store the data will be housed in a SCIF where access is limited to specific people. Again, probably 99.999% of Access developers have never been inside a SCIF.

And you don't want to hear about a friend going to jail. Been there, done that.

As I have indicated many times in the past, I will not split my database without getting a signed affidavit from the client. I occasionally suggest that I may split just to keep people off my back. And I am not about use SQL Server, DB2, Oracle, or Informix.

Going "around & around' means that folks refuse to understand that I am not splitting.
 
been a few - some less successful such as web apps. Now we have dataverse, recent improvments to graphs (appeals to some, not others), update of the web control to name a few.

These two statements are incompatible


How do you plan to maintain it? You decide on a necessary improvement, perhaps as a result of upgrades to Access/Office/Windows, are you going to ask each customer to somehow return the single file (circa 2Gb) to you for you to make the change and return it to the customer and in the meantime, they can't use it?


what exactly? - just answered by Colin.
I am still working on it, so I am fluid. The only thing that I don't want to deal with is splits. I have a partner who wants us to use them. We are like Steve Jobs and Steve Wozniak.
 
The_Doc_Man.

You mention 32-bit. I am running my database on a 64-bit laptop. Does that make any difference?

When I commercialize the application I plan to sell it with a PC. I don't want users to have any hardware problems that I don't have.
The_Doc_Man,

I checked my hard drive and the largest file size in the backup folder is 2.07Gb. I suppose that's why my database hasn't crashed.

I will keep working on it.

Just a little more and I should see smoke.

Thanks for the heads up.
 
Colin said it correctly. I have a real problem with the concept of a split database.

I learned from Mike's article that someone in the organization can change the data if he has access to a split database. I don't want that to happen.
In other words, I do not want my database to be used by multiple people. That's not what all other developers want when they develop a database. I want one person to own the database and for that person to generate reports that are distributed as PDFs. That's totally contrary to everything we try to achieve with Access.

I have my reasons for thinking this way. It is mainly about the information being company sensitive and so no one other than the owner should have access to it. In some cases, the data will be classified and in other cases, exposure could result in jail time. These are situations that 99.999% of Access developers never have to deal with. Sometimes PCs used to store the data will be housed in a SCIF where access is limited to specific people. Again, probably 99.999% of Access developers have never been inside a SCIF.

And you don't want to hear about a friend going to jail. Been there, done that.

As I have indicated many times in the past, I will not split my database without getting a signed affidavit from the client. I occasionally suggest that I may split just to keep people off my back. And I am not about use SQL Server, DB2, Oracle, or Informix.

Going "around & around' means that folks refuse to understand that I am not splitting.
You built an app on top of an insecure platform to be used within a very secure environment. I don't know, man, this is not going to end well for you, it's almost like storing money under my mattress.
 
You built an app on top of an insecure platform to be used within a very secure environment. I don't know, man, this is not going to end well for you, it's almost like storing money under my mattress.
Edgar,
I would never store money under YOUR mattress.

When I go into a SCIF, I always have two guards carry my laptop. I figure I can't be too safe.
 
Edgar,
I would never store money under YOUR mattress.

When I go into a SCIF, I always have two guards carry my laptop. I figure I can't be too safe.
Can't but only hope it works well for you, man. I'm sending some good vibes (y)(y)(y)
 

Users who are viewing this thread

Back
Top Bottom