Microsoft Access suffers from multiple personality disorder.
On the one hand, Access is a full-featured file-based relational database. And on the other hand, Access is a full-featured rapid application development tool.
The dual nature of Access–and the ignorance of this reality–is a major source of the shade that "real programmers" throw its way.
Access: File Database
Many people look down their noses and say, "There's absolutely no reason to use Microsoft Access when SQL Server Express is free." Those people are referring exclusively to Access's role as a database and completely dismissing its additional role as a rapid application development (RAD) tool.
But even if we limit the discussion to Access's role as a database, the comparison between Access and SQL Server is still wrong. Microsoft Access is a file-based database, while SQL Server is a client-server database. So, in any situation that calls for a client-server database solution, SQL Server Express is absolutely a better choice than Microsoft Access!
If you want to compare apples-to-apples for Access's database role, you need to consider another file-based database. In this arena, the most obvious comparison is SQLite. SQLite is a great product. There are plenty of situations where SQLite is a better choice than Access (cross-platform development, for one).
That said, if you are developing a Microsoft Access application and it calls for a file-based database back-end, a Microsoft Access database is the obvious choice.
Access: Rapid Application Development Tool
This is where it gets interesting. Many software developers have never bothered trying to use Microsoft Access as an application development tool.
An objective observer would be hard-pressed to dismiss Access as a RAD tool out-of-hand. If you are developing a desktop, data-heavy application–especially one that needs to integrate with other Microsoft Office applications–then Access is hard to beat.
Let's break down the three criteria I just used to qualify my statement:
Do not use Microsoft Access to develop a web application. Microsoft flirted with that idea for a couple of releases in the form of Access Web Apps. The experiment was short-lived. There are a ton of great platforms for developing web applications. Use one of those instead.
On the flip side, there are relatively few options for rapidly developing desktop applications. The only one that comes to mind is FileMaker, but that lacks the power and flexibility of an integrated programming language the way Access has VBA.
Access is a poor choice if you are developing a video editor, an image manipulation program, or a real-time chat client. But if the main purpose of your application is managing data–creating, reading, updating, and deleting–then Access is a perfect fit.
Microsoft Office Integration
Access includes charting, but it's not as good as Excel. Access provides a report builder, but it's a less capable word processor than Word. But none of that matters because Access can make use of all the features of those other programs through tightly integrated OLE automation.
So, the next time you hear someone say, "Why would you ever use Access when you could use X?" smile politely and reply, "Which Access are you talking about?" Chances are they don't even know there's a difference.