I love Access, and always have, but I will admit to it having two critical problems that killed it, both Microsoft's fault.
First, Access stores its built-in security system passwords in the clear. You can download password revealers for Access off the web. (I nearly had a heart attack when I first found that out).
MS knows about this and REFUSES to fix it because they want to sell you SQL Server for beaucoup bucks. (Multi-thousands vs a couple of hundred.) From an MS bean counter POV this is a no-brainer.
Second, the Jet engine does not scale because it's file based not server based like SQL Server. This too is a deliberate design decision. MS could easily integrate the free version of SQL Server into Access as a drop in replacement (API identical) for Jet--but they made the $$$ decision not to.
As a result the 100k line Access application that ran our SMB had issues with approximately 30 concurrent users. As a result we replaced it with a VB.Net/SQL Server which does basically the same job, only it takes 695k lines to do it, split evenly between VB.Net and SQL Server. Oh, and while the Access app took 2 years to develop (and refine over the following decade) the replacement took *9* years to develop (admittedly while doing other stuff too). Keep in mind our company has exactly 1 person for IT, development and everything else.
From a developer standpoint Access STOMPS Visual Studio. It seamlessly integrates forms, sub-forms, reports (and sub-reports), a programming language, database development tools, and more. If MS had done with SQL Server what they'd done with Jet we'd be saying "Oracle who?".
And while purists may scoff at any form of Basic, VBA was a wonderful version of Basic. Coded correctly, it was not slow, our old Access application had sub-second response times, even for most reports.
I mourn having to move on. SQL Server *is* far more scalable, no question. You can throw tons of hardware at it and it will take advantage, where Jet couldn't. It's far more secure, yes, and stored procedures are certainly easier to secure than tables.
But T/SQL *sucks* as a language, and having to learn two distinct languages to do a single project is insane.
Ah well, the road not taken and all that...