I remember there was at least one video game (some turn-based tactics game, don't recall the name) that had Access as a savegame format. It was very convenient to edit the save file.
So basically the SQLite of its day?
Acess allowed you yo build small forms/apps fast.
Those business apps made "by that one person" that allowed few other to input data concurrently (back then no google sheets / shared Excel... which are horrible and have no user defined GUI) and then to make reports.
Also data manipulation when it didnt fit Excel (I know access has 2gb limit, but it was a lot).
Between Excel (with no functional API to use for storage) and a "real database" (your users now need to ask IT to deploy SQLServer) was a use case of app just needs to store persistent data for a single user.