Access let you build visual apps, usually data-entry workflows, around its internal SQL database. You could build small apps with it using Visual Basic and a visual UI editor. Plus, all your work ships as a single file, provided the user also has Access installed. In many ways, it was like Apple’s Hypercard, but also way easier to write than webpages with the same capability. Oh, and you don’t need a server anywhere to make it work; it’s 100% local. It was also the next logical step to take after the most complex things you can do in Excel.

That said, it was crippled from the start - still very useful, but not for heavyweight stuff. It’s limited to a fixed number of UI, pages, database rows, etc, so it wouldn’t compete with more expensive MS solutions (this thing came with Office). I don’t think it got a lot of love because of that, but I personally used it to solve some real problems in the workplace, without need of any (official) developer resources.

In the present day, it would actually compete with a lot of simple business cases that are served in the cloud at some cost.