Access is not a DBMS. Or at least it’s not just a simple DBMS. It’s a very good RAD environment, a simple way to create SQL code graphically, and a regular front-end to fully fledged DBMs.
Neither SQL Server (Express or MSDE) nor Oracle, MySQL, etc. will ever replace it, until they come integrated with a simple programming language, a Crystal Reports like facility and a way for beginners to get around without having to learn SQL.
At my first professional job I developed a very big system completely in Access. Front end for the clients, admin front for me, reports and monitoring for management, permissions per user, automatic tasks run at certain times, etc. I came to learn a lot of its flaws and strengths as a result.
I’ve seen marvelous apps done with it, as well as pieces of crap. I still use it for personal projects, and ain’t’ ashamed of it (for instance, a Sudoku player, or a Karnaugh mapping implementation). There’s an MVP who’s created a Paint clone completely in Access, though I believe that’s extreme.
Access’ pearls: It’s nice to easily test a database design idea and have sketch forms, reports, etc. created for you. If you change a column’s name (or even a table, though that fails sometimes) it’s nice to see all references to that have changed to the new name, automatically. The “sub-form” control rocks, I longed for it on VB6. And the “Thunder” button to do repeated filtering on tables is great, I wish I had something like that on SSMS!