Ever wonder when you should actually avoid using Microsoft Access?
Hey, don’t get me wrong, Access is a great product, but it can’t do it all and a good developer needs to recognize limitations and select to use alternate technologies if/when required by the mandate.
In this discussion, I am talking about using Access for both the Front-End and Back-End.
Windows Only
One of Access’ major Achilles heels is the simple fact that it is only available on Windows OSes. It isn’t available for MAC, Linux, … There are no mobile app versions of mobile devices, tablet. There is no web version unlike Outlook, Excel, Word, PowerPoint, …
This can however be circumvented by using remote software like RDP, CITRIX, … if one has a Windows machine available.
Storage Limit
Another major limitation is that Access currently has a 2GB file size limit. Thus, it is not suitable to large enterprise quantities of data.
That said, this can be alleviated by:
- never embedding attachments within an Access database
- using an alternate RDMS (PostgreSQL, MySQL, SQL Server, …) to house the data while still utilizing Access for the interface.
Sharing Limitations
Yet one more issue is needing to run Access via a LAN. Access is meant to run within a hardwired office space and was never intended to be shared over wireless networks, little alone over the Internet.
Once again, by employing an alternate RDMS for the back-end data storage it is possible to get around such limitation. Alternate solutions include using technologies such as: RDP, Terminal Services, CITRIX, …
Limited Number of Simultaneous Users
I often read people stating that Access is limited to a single user otherwise it will corrupt. This is simply false. The fact is that a properly designed and split Access database can support +/-40-80 users depending on the network…
However, if you need to support even more simultaneous users, then once again switching your back-end to an alternate RDMS will allow you to support 100s or even 1000s of simultaneous users.
Security
One major issue that has been presented to me is the fact that Access databases are flat file databases and they can simply be copied to USB drive, emailed…
While this is true, this is no different than any other document within a company. Excel, Word, even Outlook PSTs can be copied at any time by anyone and taken offsite.
Moreover, this is once again an issue where you can alleviate the problem by migrating the database back-end to an alternate RDMS and thus gain much more control on data access.
Old
Another common comment is it looks old. It doesn’t look and feel modern.
By default, I’d tend to agree.
That said, you can jazz things up if you want to take the time. It requires effort to create custom images for button, custom ribbon, … but this is all available to you to create things as you please.
However, you will never come close to the types of designs that we see today online. What CSS, JS, … permit web developers is at another level when compared to what is available to developers in Access.
Even the Theme capabilities in Access, which should have enabled a certain modernization of interface design, is atrocious to the point most don’t use it.
BUGS!
For me, this is Access’ Biggest Issue! I, myself, have lost a couple clients after updates broke their databases and left them stranded, unable to access business critical systems and have others request we develop replacement for their Access databases using alternate technologies.
I know the Dev Team actively works to resolve them, we see it published on their blog, but every new update seems to also introduce new issues. It the never ending cycle where the end-users have become testing guinea pigs for Microsoft. Case and point, a new features like the Modern Web Browser control, is filled with bugs from a total lack of internal QA before public release.
Market Perception
This is another MAJOR issue. The fact of the matter is whether we like it or not, Microsoft Access is looked down upon by most fellow developers, IT Admins, … thus making it a questionable choice. 9 times out of 10 it will be an uphill battle to get Microsoft Access installed, supported, … so you have to ask yourself from the start if it truly is the best tool for the job. Are you up for the fight?
Conclusion
A couple reasons do exists for avoiding Access , but I hope most people can see a recurring theme above, in most cases, switching back-end technologies provides a solution.
So for local projects, smaller projects (+/-50 users or less) Access remains a good all around potential solution.
For everything else, it is time to switch back-end technologies to one of a great many alternate RDMS: MySQL, PostgreSQL, SQL Server, SQL Azure, …, but continue to use Access for front-end development (if desired).
The above obviously goes hand in hand with proper development, like knowing to split a database, providing unique copies of the front-end to each user, so on and so forth.
As for BUGs, you either learn to live with them or use other, more stable technologies: .net, PHP, C#, …
I love Access, but have to agree with most of your points. They should have a way where you can EASILY rollback to a previously known good version in case an update introduces an unexpected bug… Like the old days when Access 2007 broke your database… We’ll just reinstall 2003.
Agreed. I have mentioned numerous times that the build management is ridiculous. There should be a simple GUI interface to enable user to easily switch Update Channels and Builds. Requiring users to use DOS commands, XML configurations, … is just plain stupid! I don’t get how after CTR being released since +/-2015 this still hasn’t been addressed.
I have to say “previously known good version” is quite the term, not sure such a thing exists anymore. Every build seems to come with some compromise.
I’ve been working in Access 2021, and I can’t imagine how Office 365 (er, Microsoft 365), with always having the “latest version” always at the ready, is going to destroy users.
It’s simple, every update (or new version) has new bugs. It is these instabilities and continuous bugs that make using Access problematic. Add to that the fact that 8+ years and Microsoft still hasn’t given us proper GUI tools to configure the Update Channel and Build making it next to impossible for the average user to handle.
If we look at the larger picture, taking a user previously running 2003, 2007, … migrating to MS365 then their is a loss of compatibility between mdb formats, the loss of a variety of features, ADP, Pivot, …
A concrete example, I had a client running the mission critical systems on Access, overnight one day they received an update and suddenly the system was down. Now it take them some time to contact me, IT is going nuts. I start digging (this is a new update with a bug, so nothing is out on the subject yet). Hours go by, have to request IT rollback the update, … They loose 10000s of dollars in salaries, orders, … all because of having the latest version. This client was the first one to make me convert their entire operation to a web based system and we have never looked back!
The real issue is Microsoft’s Internal QA, which is non-existent. They now release into the wild half-baked feature and wait for users to report all the problems and do reactive development. If this wasn’t the case and things were properly tested, then there would be absolutely no issue to always staying current.
Finally, you are running 2021 which doesn’t get feature updates, only security and bug fixes. So you don’t experience the same issues that MS365 users do. Yours is relatively stable, there’s is one of ups and downs.
I could go on, but this subject has been covered before and I have to run.
If you’re using RDP or Citrix , you can run MS Access app in android, mac, linux or even run in browsers like (
https://www.cybelesoft.com/thinfinity/virtualui/) and Windows server 2016 above also supports HTML RDP client. For security, of course you have to locked down the Windows machine, and use SQL Server Express or any ODBC compliant RDBMS as backend. Report writer is the main reason why I am sticking with Access for 20 years+ and I am pleased with C#/ASP.Net webforms if my client want to extend the Access App to the web. No Swiss knife developer tool exists.The BUGS! OMG the Bugs. I’ve been working on porting an old business app with 30 years of usage and business logic for the past year. So far, I’ve spent one year wrestling with the bugs de jour of MS. Stupid bugs that are well documented in forums to exist, often five or more years old. The old software comes from 80s “minis” where millions of transactions processed US$ 18B without losing a penny. They did it with 50 year-old software on equipment thousands of times less powerful than Access should be on a PC. The 40-80 user position fills the bill, as should ACE, but the bugs! As a result, only the least important module of the project is 50% complete, after a year! The original four (much larger) modules were, by comparison, finished in six months on the mini.
In other words, ACE should be good enough for the back-end and it may be. I’ll probably never know because of the horrible support MS has for the front-end.