What's a good & defensible use case for Access? It always felt like a worst-of-both-worlds package both in terms of functionality as well as UI / UX.
The good: it is actually not a bad tool if you're familiar with database concepts. It even has some nice features, like being able to see a graph of all the pk relationships between tables (and use that graphical tool to create relationships and rules). You can seamlessly switch between list view (rows)/design view (GUI aid)/SQL when you're working on queries, allowing you to test things quickly and get rapid feedback at different levels of detail. It has a not-completely-insane tool for generating custom reports from tables or queries. It is extensible with VBA (trust me, I know -- that's not really a ringing endorsement). And like many MS products it has wizards for common tasks that make them surprisingly painless. And above all, because Access is part of the office suite, a regular user might actually have the program installed and be allowed to use it in a BigCorp or Government setting.
All that being said... Access is abused just like Excel by people who don't really know what it's for. What's worse, you get monstrosities made by people with just enough knowledge of databases to make them dangerous but without the wisdom to consider normalization, maintainability, or anything like that. I have so often been asked to "take a look at" an Access database that was thrown together 10+ years ago by a mildly computer savvy amateur that is still used in production, and without exception the insides are horrifying. (Side note: usually I have had to fight my instinct to take these on as a pet project because nothing good can possibly come of it, and as soon as you change it you own it).
TL;DR -- You already know shit. You might actually be surprised how not-bad Access is. Sadly you are not the person who is using Access in 99.9999% of cases.
If you know what you're doing, you can use Access (scary program that people don't really know what it's for) to lock down the data and enforce business rules and that kind of thing, then give coworkers Excel spreadsheets with pivot tables from an ODBC connection to the Access db that they can "do their thing" and mess with and email around.
The true worst of both worlds though is when somebody creates an amateur Access db, locks it down so you have to do everything through a 1995-Visual-Basic-looking switchboard, creates horrendous forms with garish colors and giant bitmap images that have no coherent UI... and ... I can't even go on, these are too painful to remember.
Also, most people are comfortable with Excel. Access userforms can scare the crap out of some users, but they're able to manipulate Excel just fine.
I was able to whip up a tool for my boss where his direct reports could log the time they spend on a particular project each day (ridiculous, I know). It's a simple Excel spreadsheet with Excels built in calendar selector, and two columns: Project and hours. Clicking a button writes to an Access database, which my boss can now pull the data straight into Excel with a couple canned reports. No one ever sees anything but Excel. I get that this may not be ideal but: 1. Took a morning to get to production 2. Quick user uptake because they're already comfortable with the system 3. Gets the job done, and my boss can still mess around with the numbers in excel all he likes
So there are use cases.
Another one that I've used successfully is utilizing Access as a middle-man to join two discrete systems within a corporation by using the Import Linked Table feature and building a join query. This way, Access does the heavy lifting of mashing two separate datasets together, allowing users to understand relationships instead of spending time trying to jam lines of data side-by-side.
This comment got long...sorry.