What was/is your "largest" access project

You all have some pretty impressive resumes. To all the members, is Access your go-to solution for database user interface and/or management?
I think nothing can beat MS Access connected to an SQL Server. I've used a couple of accounting, inventory and payroll software but nothing can beat MS Access. It's so customizable to the thousands of different data analytics management usually wants, that no other out-of-the-box solution can provide.

And now that Excel comes with Power Query/M Code, reporting just got 1000 times more beautiful.
 
I use Sage as my accounting software but NEVER do data entry in it. I have all the employees use Access or Excel (depending on their experience) to do Data Entry then run some queries to filter out errors and trust me you will get errors when people who are not too familiar with spreadsheets are doing data entry. Then I take the corrected data and upload it into Sage.

When it's time for Audit, my Sage is clean, no Voids, no Gaps, no Errors.
 
Last edited:
Very interesting experience
What mean exactly 'Access BE was 500mb' ?
If you were using Sql Server isn't that the backend?
Sorry for the delay in my response...

The ERP started out with an Access BE (Back-end) Database. We had an FE (Front-end) User Interface that was distributed to each User's Computer. The FE was linked to the Access BE that was located on a dedicated computer. PS. I never used Mapped Drives in the Table Links, always used UNC Method as it was more reliable.

We migrated the Access BE to SQL Server when it was 500 mb (megabytes). The Access BE even at 500 MB was very manageable. It was only after more than 10 employees connecting to it did we began to have issues. Once migrated to SQL Server, we no longer had that issue. And then it was expanded to the many.
 
I have been slowy growing a custom CRM at work which now has over 200 tables, over 300 forms across all the different apps I have made.

I use custom ribbons and have some code I am very proud of to do tasks for us. My best one of late is product BOMs which works out cost price, labour, price breaks, margins and I can export this into our 3rd party (CIM50) manufacturing software.

The main use of it is for customer quotes which has grown significantly since I picked up an old Access '95 manual when I was bored one day back in 2003.

We have 15+ concurrent users. It ties into Sage Accounts and CIM50. I would be more than happy for anyone who does Access as a main job to have a look at it to see where I have gone wrong to make it work.

~Matt
 
200 tables sounds like a lot. Do you have PKs and relationships defined and RI enforced? If you have a problem with the app, you need to be specific regarding where you need the help.
Hi Pat,

There are no issue with my database. I was only asking if anyone would like to see it to offer me ways to improve it further.

~Matt
 
Last edited:
Well the difference is that if you use Access you can do whatever you want. If you use a third party product you can only do whatever they allow you to do within the interface

In point of fact Access is so bloody powerful that a lot of code we write is explicitly there to prevent a user doing whatever they want, including the developer. In Access, (or any other database system actually) it's easily possible to completely trash your own data with an injudicious command, (such as update a field to a new value without specifying a particular row), and there's no undo.

So the developer provides a set of facilities that are likely to be required. If clients have new requirements, then the developer provides additional facilities, or deals with their requirements as a one-off process.

@MattBaldry. I have to say 200 tables sounds a lot for only 300 forms. My biggest projects have around 100 tables, but thousands of queries, hundreds of forms and reports, and a menu system with hundreds of choices.
 
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.
 
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.

Having that many forms and reports is not necessarily out of the question, but having 4100 tables - and having that many fields in some - usually is a flare-lit signal that something might be a bit off. Since you said it is over 2 Gb, you obvious have one of two cases: (a) an independent SQL engine as the backend or (b) more than one native Access backend, with disjoint table spaces.
 
No sir.
I don't touch SQL. I have one Access file that is not split. I frequently run C&R. I am not sure why the database has not crashed. Perhaps 2 Gb is not real. I have been teetering on it for years. I back up the application every few days, just in case.
 
No sir.
I don't touch SQL. I have one Access file that is not split. I frequently run C&R. I am not sure why the database has not crashed. Perhaps 2 Gb is not real. I have been teetering on it for years. I back up the application every few days, just in case.
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.
 
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.
The CR will actually stop working way before the 2gb mark, probably closer to 1.85 it will choke.

As for 4100 tables, likely a lot of temporary or analytical tables that need to be removed. In the Manufacturing ERP that I developed, which was highly normalized, had just shy of 100 tables. It did everything from automated inventory, all cost methods, to sales, as well as connected to AutoCAD and MasterCam, to annual reporting and budgeting. It also facilitated over 80 branch plants. Back in the 90’s before we got our WAN I would merge all 80 into 1 at month end. Then we placed the data on SQL Server 97. But before the migration to SQL server, the data can only be in a database by itself on. No forms, etc and I know., lot backend. Migrating to SQL server was a huge relief.

Very few ever get to this point in a legitimate application. In most case where I have to recover client databases it’s because of total mismanagement of tables. Data would be imported from flat files that were not normalized, and the date itself was never normalized in a lot of temporary tables that were not deleted or cleaned up after a process. Even if you develop and ETL process that may require several tables, but you need to maintain a structure. The data at least should be deleted from them when a process completes.
 
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.
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?
 
I actually had a text-to-speech option in my big Navy database. Took me ONE VERSION to make the voice optional. (By popular demand, with torches and pitchforks assembling near my desk.)
 
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
 

Users who are viewing this thread

Back
Top Bottom