What is Microsoft Access Used For?

A Brief MS Access Tutorial

Very simply, Microsoft Access is an information management tool that helps you store information for reference, reporting, and analysis. Microsoft Access can help you overcome the limitations found when trying to manage large amounts of information in Microsoft Excel or other spreadsheet applications.
Bookmark this page

Table of Contents

The Problem The Solution How MS Access Works When Should I Use Microsoft Access? Getting Started with Access Do I need MS Access 2007 or Access 2010? How do I access Microsoft Access? What if I don't have time to learn Access Database design?

The Problem

A spreadsheet program is a terrific tool for maintaining and calculating small sets of information. Microsoft Excel is easy to understand, create column names, enter your data, create formulas, and you're on your way. You can sort, filter, and format the data quickly and easily. But spreadsheets are not ideal for handling hundreds of records where you need to have "one version of the truth" for something like a customer, a contact, or main "thing" you need to track. It is very easy to introduce errors into a spreadsheet, which then makes analysis, summing, and reporting very challenging. The image below provides a quick example of several key challenges spreadsheets face.

Microsoft Excel Spreadsheet Challenges - MS Access can solve

The Solution: Store Related Information Together, Eliminate Duplication

The value any database can provide is to store related information in one place, and then let you connect various different things together (sometimes called "entities" in database speak). You store one version of the truth for any given thing, like a client, a DVD, or an order. Some of the major benefits to using a database are:

Fewer errors and inconsistencies. Maintaining one version of the truth for the things you need to track minimizes the potential for duplication, errors, and inconsistent values. In the Excel example above, customer names, contact phone numbers, and product names are misspelled, transposed, and abbreviated in different forms. Imagine trying to create a report or graph in Excel that shows an accurate picture of your sales or other key performance indicators.
Higher productivity. You and others working in your database need only change a single record and all other related things in your database will automatically "see" the change. In our Excel example, you would need to change every occurrence of a customer's name. Find and replace wouldn't necessarily catch that a customer's name is spelled six different ways. In a database, there is only one customer record to change. Every other record (orders, contacts, etc) that relates to that customer record will never need to change because they are joined to the customer.

Helpful Access Tools

Designer
Easy-to-use wizard helps you create Access databases in minutes without being an expert

UI Builder
Essential features for any Access application

PowerPane for Excel
Get more from Microsoft Excel faster

Visit our Microsoft Access catalogue for more products

How does a Microsoft Access database work?

Microsoft Access works in the same manner any database does, by storing related information together, and letting you create connections (commonly called relationships) between different things. The relationships between two different things in MSAccess can be very simple (such as a contact at a customer and the customer itself) or complex. In the example below, the blue boxes represent the major things we're tracking in our MS Access database tables, and the reports at right show how you can join the related information for analysis and reporting.

Microsoft Access database relationships - the key place where MSAccess offers value

Data is stored in Microsoft Access tables (think of them as mini-spreadsheets that store only one type of thing). A table can have many fields (think of them as columns in your spreadsheet). Each field in a table can be set up to allow or prevent users from entering certain information (for example, you could say one field only accepts dates, another can only allow a user to enter a numeric value, while another lets them enter anything they want).

Once you have your MS Access tables, fields, and relationships set up, you can create data entry forms that use those tables to store your information and later create reports with the data. Microsoft Access forms are incredibly easy (and fun) to design with a wysiwyg form design tool. And you can use MS Access forms to simplify data entry for users by grouping related fields together, and hiding fields they don't need to enter. The Microsoft Access Command Button Wizard even helps you to create simple buttons for your forms without understanding how to create macros and Visual Basic.

When should I use Microsoft Access?

We've put together the following comparison table to help you decide whether Microsoft Access may be the right solution for you.

Use	Spreadsheet (Excel)	Desktop Database (MS Access)	SQL Server or MySQL with Access as the user interface
Validate basic user input (e.g., for a single field/cell	Yes	Yes	Yes
Complex user input validation or events (if field X = Y, and field Z is empty, perform some action)	Challenging to accomplish	Yes	Yes
Data arranged in a few columns without much repetition	Yes	Yes	Yes
Rows/records supported	Excel 2000 and Excel 2003: 65,535 rows Excel 2007 and Excel 2010: 1,048,576 rows	Unlimited (up to 2GB/table)	Unlimited
Simple cross-references	Yes (vlookup and hlookup)	Yes	Yes
Complex data analysis/queries	Limited	Yes	Yes
Events based on user actions (when user exits a field/cell do X, when they mouse over a field/cell do Y)	No	Yes	Yes
Multiple users, but generally accessing data at different times	No	Yes	Yes
Multiple users, accessing data at the same time	No	Yes (but can be slow depending on network speed)	Yes
Frequent mass-update operations	Challenging to accomplish, time intensive	Yes	Yes
Multiple data entry forms	No	Yes	Yes
Mail Merge with Word	Yes	Yes	Yes
Reporting	Challenging to accomplish	Yes	Yes
Automated backups	Challenging to accomplish	Possible with VB coding, backup software,or UI Builder for Microsoft Access	Yes
Record-level auditing	No	Possible with VB coding or UI Builder for Microsoft Access	Yes
Ability to hide the inner workings of your project from the user	Challenging to accomplish	Yes (MDE files and ACCDE files)	Yes
Expertise Required	Low	For simple databases: Low/Moderate For complex requirements: Moderate-to-High	High
Attaching files/links to fields	No	Yes (MS Access 2007 and Access 2010)	Yes
Sharing information on the Web	Yes (Excel 2007 and Excel 2010)	Yes (Microsoft Access 2010)	Yes (with a web front-end like PHP or ASP.NET)

How do I get started with MS Access?

With millions of users, there is a vast wealth of resources to help you. From message boards, Access books, tutorials, blogs, and Microsoft Access addins, you will have many resources to help you succeed. OpenGate Software has an entire MSAccess Resource Center dedicated to Microsoft Access as well.

Do I need MS Access 2007 or MSAccess 2010?

Access 2000 and Access 2003 remain widely used. You do not need to upgrade to Access 2007 or Access 2010 to see the benefits of using a database compared to Excel. Best of all, if you design your database in Access 2000, Access 2003, or Access XP, you can migrate to Access 2007 or Access 2010 when you decide to purchase the upgrade license. OpenGate has also created a helpful comparison of MSAccess Versions from 2000 to 2010.

How do I access Microsoft Access?

To find out if you have MSAccess installed on your machine, select Start>>Programs>>Microsoft Office. In the Microsoft Office start meny folder you will see Microsoft Access listed if it is installed. Microsoft Office Access comes with the Professional Edition of Office, or can be purchased separately. Many companies purchase and install Access by default.

What if I don't have time to learn about Access Database design?

Que the shameless product plug! Our Designer for Microsoft Access product is meant to save you hours (literally, hours) of time reading about Access, trial and error creating your database, not to mention the frustration. Designer walks you through a Q&A wizard in plain language. You tell Designer what you want to track in your database, and how you want it to look. Designer does all the heavy lifting like creating relationships, fields, data types, indexes, and data entry forms. Don't believe us? Learn more about Designer for MSAccess Watch the Demo Video Take a Free Test Drive