Microsoft Access provides two main functions.
Firstly it is a database container, capable of storing your data.
Secondly it is an application development tool which enables you to create forms and reports that make use of your data by presenting it to the user.
In fact Access is considered a Rapid Application Development (RAD) tool.
It’s considered good form to keep the two separate using two Access Databases. One for the tables that hold the data and the other, a presentation application, that holds all the forms and reports that the user interacts with.
Rapid Application Development
Due to its visual capabilities (you can layout your forms and reports by dragging and dropping controls so What you See is What you Get) Access is about the fastest development environment you will find.
It’s quite easy for a beginner to get started with yet enormously powerful in what it can deliver in the hands of an experienced developer.
Combined with SQL Server, Access is capable of running Very Big Organisations.
Data Storage
As a data storage facility, it is simple to setup and use. Any Windows machine is capable of hosting an Access Database even without Access Installed.
The size of the database is limited to 2Gb. But that’s still a lot of data storage.
Once you start to get dozens of users and lots of data (millions of records) then you may want to upsize your data storage to SQL Server.
This is not as hard as it used to be and, depending on the amount of data storage you need, the free version may be all that you need.
Relational Database
Access is a Relational Database. That means that you can create Relationships between tables to Normalise your data. For example, if you have an Invoice Table then it needs just one column that identifies the customer and then all the customer’s details can be retrieved from the Customer table. If the Customer’s details change then you only need to change the data in once place.
Relationships also allow us to enforce Referential Integrity. In the example of an Invoice, the Customer’s ID must have a related record in the Customer Table.
A Relational Database can therefore provide structure and validation to your data more easily than you could with spreadsheets. More on Normalisation