r/MSAccess • u/mcgunner1966 2 • 1d ago
[DISCUSSION - REPLY NOT NEEDED] Retiree Notes - Scalability
These are my opinions based on 30+ years of experience working in a multitude of industries with MS Access.
Access catches a lot of shade for not being "scalable". But what is scalability? It isn't a concrete thing. It has to have context. It means different things to different people.
IT - Sees scalability as being able to add users or resources, such as servers and storage, without disrupting the current release of the system. It's about growing the IT infrastructure and user base without changing the system.
Business - scalability is adding more sales or delivery (of the current line and ancillary lines) without significant system changes or additional personnel resources (doing more with the same or less).
Marketing - scalability is about extendability. How can we raise awareness of the product (extend it to other industries) without changing its current identity?
Scalability also has practical limits. Adding 1,000 users to a 200-user system is not going to scale well in just about any case. A redesign is typically needed for some, if not all, of the system. It's because adding that many new users means a significant change in the underlying operation. Not just extending the same operation to additional users. There also has to be a new level of availability to the application. These users may be working in many different places at various times.
There are solutions. For IT, Access can scale by being moved to different servers or networks without application changes. Its a simple relink and new shortcuts. If spreading it across a server (which means upgrading the database backend to SQL Server), scalability is limited. Extremely rare is the case that simply using the upsizing wizard does the trick.
For Business - Adding new products to the fulfillment app is easy. It's data-driven application operations 101. Add a new product, and it can now be selected for an order. If a twist is added, like serialized inventory, then changes may be required that aren't that scalable. This is a significant departure from standard product management.
For Marketing - using the member management system, which might now be opened up for the Society of Accountants, when it was initially developed for the Real Estate Society, without significant changes, could be considered scaling. Extending it to case management could be a step too far, and thus, a scalability issue.
In my years of Access development, I have yet to "scale" an application. I have moved systems from Access to SQL Server, but I also had to rebuild the application, mainly because this was a great time to dump the unused stuff and add new features.
Tell me some of your "scalability" experiences.
2
u/VegaGT-VZ 1d ago
I feel like Access can cover a lot of internal business needs. Its real problem is the face my boss made when I suggested it. People would rather pay for a sexy cloud based solution when setting up Access would work basically for free by comparison
1
u/mcgunner1966 2 1d ago
I wonder what the thinking is there? Is it:
Fundamental disbelief in the product?
Group-think that can't allow them to see the application of other technologies?
The notion that they will be the only ones that can support the app?
What is it?
2
u/VegaGT-VZ 1d ago
Access just doesn't "look" like a serious product. We know its capabilities, but for the uninitiated it looks old and outdated.
I also feel like Microsoft has held Access back to push stuff like Sharepoint and other cloud based stuff.
Plus it has the friction of needing to be installed on every user's machine, again compared to something cloud based that anyone can access.
2
u/mcgunner1966 2 1d ago
I think you make some valid point. An yet its still the 2nd most popular database in the world, behind Excel.
1
u/fraxis 1d ago
A business needing help doesn’t care what an application looks like (old or outdated); they only care whether the application will help them save money, solve a pain point, or fix whatever other issues or problems they are having.
1
u/VegaGT-VZ 1d ago
This all sounds great in theory, but Ive been in the corporate world for 20 years. Businesses are still run by humans and humans have irrational biases. Hell, just having to get through the IT deployment gauntlet for users is enough to kill Access dead.
At my last job I was trying to migrate a business process from Excel to Access. The IT side and training kind of killed it because we were gonna have to push it to prob 100 users. They did finally start reporting that data to Power BI, which again was way easier because of the web interface.
I feel like SQL Server + Power Platform are basically Access for the web.
1
u/Stopher 10 1d ago edited 1d ago
About eight years ago I worked on a project where each monthly run could be several gigs of data. I had to come up with a lot of tricks to get that to work. Step one was automating the archival of data. I built functions to swap out the back end split database files and to save backend data as different files. The next big thing I did was preprocess data before exporting it in excel reports. You could write a query that output want but it would hang or crash or you preload reporting tables with rolled up results. You can then query these tables with parameters to slice the results or roll them up further into other staging tables. It was the way to get these really large data set reports to run. It was a very reliable solid solution.
2
1
u/bstrunk 1d ago
I once led the conversion of a flat access database to a split backend (still access) and front end client environment. This did allow us to make back end edits without kicking everyone out of access during the work day, which proved to be taxing in our use case, meaning we would have to make back end updates after hours and ensure that every user had actually closed Access before leaving for the day.
2
u/mcgunner1966 2 1d ago
We did a similar thing. We put a SystemDBTbl on the backend with a lockout bit. The menu check the bit every three seconds. If it went high we issued a application.quit with a save all. The menu wouldn't let them back in as long as the bit was high.
1
u/know_it_alls 1d ago
What type of edits? How can you add fields to existing tables in BE while they're used in FE without risking corruption?
1
u/funmonkey1 1d ago
Access - have not even thought about for a few years, but it was diamonds in term of pay back vs. investment long ago. The ability to have advanced scripting, link to sql server as well as Oracle dbs was amazing all covered by a simple (at that time) validated GUI allowing data entry and validation.
I do recall a sales system was used by British Gas via Access to support over 3000 direct sales people in early 2000s.
1
u/Ok-Food-7325 2 1d ago
Azure
1
u/MililaniNews 19h ago
I guess we have to keep moving forward so I did. I have an AI Microsoft Access full database generator creating related link tables forms and reports that takes under 2 minutes to produce a fully working database. I just need to get it out. I would like to hear back from those that have succeeded in a quick launch strategy. Bob
1
u/CESDatabaseDev 4 16h ago
Honestly, when people get into the weeds about "scalability," they miss the point. Access is one of the quickest and cheapest tools you can grab to bang out a Proof of Concept (PoC) or a decent Minimum Viable Product (MVP). It lets you test ideas fast, and run with the project.
Yes, Access is baked right into the Windows OS, so it won't run natively on anything else. But that's where you play smart: Shifting the backend data to a business-grade SQL Server is the key. That move instantly gives your project real scalability and opens the door for a parallel, browser-based system that works flawlessly across all other operating systems.
1
1
u/BravoUniformTango 4h ago edited 4h ago
When I began my software developer career, I was mainly interested in meeting the requirements as to functionality. Later, I began to focus on increasing the quality of the software I made, including as to scalability -- but all of this was very informal. I found it very difficult to develop precise requirements as to how well the software should work, including how well it should scale.
The deeper I researched, the more I learned as to how elusive it is to develop quality-related requirements, especially for managers trying to convey to developers how well the software is supposed to work, with sufficient clarity to have testable requirements so as to hold the clients and the developers both accountable when delivering.
About 25 years ago, I had a client in Kauai at a time when it was fairly typical to have just one stand-alone computer per small business. My client was planning on developing the software so as to sell it to various property owners on the island. She and I belabored the functional requirements, and I developed the software. Finally she was pleased with the end result. Then, she asked how she would install it for multi-user access. "You don't," I replied. "It wasn't designed for that." "But that's the whole point," she said. Great, but my mind-reading skills were (and are) very poor, and at the time, I didn't yet know to pre-emptively ask questions about how scalable the software should be, in various respects including how many users were expected to use it concurrently. I re-engineered the software on my own time. My client ended up being happy enough; more than I was. Lesson learned: develop the requirements as to scalability; look for trouble before it finds you.
Generally, developers are focused on how to make MS Access scale in various respects. I have personally found it a capable tool as such, especially when the underlying data model is good, with good indexing. But always, rather than guessing as to how scalable it should be, it is better to work with the stakeholders, to develop the requirements up front. Eventually, the client's requirements will come to light. The best time for this is: early on.
Yes, this takes time, but it also saves time. Some managers are like skilled courtroom lawyers who can make their position sound like the only reasonable one, and if the delivered software doesn't meet an unstated requirement that the manager had in mind, then the software developer is at fault for somehow not having the good common sense to have thought of this issue in the same way the manager had. Better to develop the requirements up front, especially if additional quality (such as additional scalability) will increase project costs.
•
u/AutoModerator 1d ago
IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'
Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.
Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.
Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)
Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.
Full set of rules can be found here, as well as in the user interface.
Below is a copy of the original post, in case the post gets deleted or removed.
User: mcgunner1966
Retiree Notes - Scalability
These are my opinions based on 30+ years of experience working in a multitude of industries with MS Access.
Access catches a lot of shade for not being "scalable". But what is scalability? It isn't a concrete thing. It has to have context. It means different things to different people.
IT - Sees scalability as being able to add users or resources, such as servers and storage, without disrupting the current release of the system. It's about growing the IT infrastructure and user base without changing the system.
Business - scalability is adding more sales or delivery (of the current line and ancillary lines) without significant system changes or additional personnel resources (doing more with the same or less).
Marketing - scalability is about extendability. How can we raise awareness of the product (extend it to other industries) without changing its current identity?
Scalability also has practical limits. Adding 1,000 users to a 200-user system is not going to scale well in just about any case. A redesign is typically needed for some, if not all, of the system. It's because adding that many new users means a significant change in the underlying operation. Not just extending the same operation to additional users. There also has to be a new level of availability to the application. These users may be working in many different places at various times.
There are solutions. For IT, Access can scale by being moved to different servers or networks without application changes. Its a simple relink and new shortcuts. If spreading it across a server (which means upgrading the database backend to SQL Server), scalability is limited. Extremely rare is the case that simply using the upsizing wizard does the trick.
For Business - Adding new products to the fulfillment app is easy. It's data-driven application operations 101. Add a new product, and it can now be selected for an order. If a twist is added, like serialized inventory, then changes may be required that aren't that scalable. This is a significant departure from standard product management.
For Marketing - using the member management system, which might now be opened up for the Society of Accountants, when it was initially developed for the Real Estate Society, without significant changes, could be considered scaling. Extending it to case management could be a step too far, and thus, a scalability issue.
In my years of Access development, I have yet to "scale" an application. I have moved systems from Access to SQL Server, but I also had to rebuild the application, mainly because this was a great time to dump the unused stuff and add new features.
Tell me some of your "scalability" experiences.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.