Skip to content
Tutorials Center
My learning

Topics

Lesson 1 of 36 ยท Getting Started

What Is Microsoft Access?

Learn what Microsoft Access is, what a desktop relational database does, which objects an Access file holds, and when to choose Access over Excel or SQL Server.

Microsoft Access is a desktop relational database program that ships with Microsoft 365 and Office Professional editions. It lets you store structured information in tables, link those tables together, search them with queries, enter data through forms and print results as reports. Everything lives in one file, so a small team can build a working application without a server, a database administrator or a web developer.

People use Access for contact lists, inventory, membership and event registration, order tracking, equipment loans, school records and many other jobs where a spreadsheet starts to creak. This lesson explains what Access is, what is inside an Access file and how to decide whether it is the right tool for your problem. Later lessons walk through each piece in detail.

Databases versus spreadsheets

A spreadsheet is a grid. You can type anything in any cell, which is flexible but easy to get wrong. A database is stricter on purpose: every column has a type, every row has the same shape, and rules stop bad data from getting in.

Imagine you track customer orders in Excel. Each row repeats the customer's name, address and phone number for every order. When a customer moves, you must find and edit every row, and one missed row leaves two addresses for the same person. A relational database keeps each fact in one place. Customers go in a Customers table, orders go in an Orders table, and each order stores only a customer number that points back.

Spreadsheet or database?

Spreadsheet One flat grid, great for calculations
Database Linked tables, one fact in one place

Access is built for the second job.

1 / 3

Here is the idea in SQL. Access speaks a dialect of SQL behind its visual designers, and the runnable examples in this tutorial use standard SQL that works the same way. This first example builds the two tables from the paragraph above and joins them.

SQL One fact in one place

Press Run (or Ctrl + Enter) to see the result here.

Both orders for Maple Cafe now show York, because the city lives in exactly one row. That is the core promise of a relational database.

The objects in an Access database

An Access file holds several kinds of objects. The Navigation Pane on the left of the window lists them.

Object What it does
Table Stores data in rows (records) and columns (fields). The foundation of everything else.
Query Selects, sorts, combines or changes data. Also used to calculate totals.
Form A screen for entering, viewing and editing records.
Report A formatted printout or PDF of data, with grouping and totals.
Macro A stored list of actions, such as opening a form or exporting a report, built without code.
Module A container for VBA (Visual Basic for Applications) code for complex logic.

You do not need all six. Many useful databases are just tables, a few queries and one form. You can add macros and modules later.

An Access record is one row, such as one customer. A field is one column, such as City. You will hear both words constantly.

What Access is good at

  • Rapid development. Wizards and designers let you build a table, form and report in an afternoon.
  • Relational structure. Relationships and referential integrity keep related data consistent.
  • Querying. The visual query designer lets you combine tables without writing SQL, and you can switch to SQL view at any time.
  • A single portable file. An .accdb file can be emailed, backed up by copying and opened on any computer with Access or the free Access Runtime.
  • Integration with Office. You can import from and export to Excel, Outlook, Word, SharePoint lists and many other sources.

What Access is not good at

Be honest about the limits, because they decide when you should move to something bigger.

  • Windows only. Access runs on Windows. There is no Mac version, and there is no Access in a browser.
  • Size limit. A single .accdb file cannot exceed 2 GB, including system objects. That is a lot of text and numbers, but attachments and pictures can fill it quickly.
  • Concurrent users. A shared Access file works well for roughly a handful to a few dozen users on a fast local network. Beyond that, or over slow connections, it struggles.
  • Web and mobile. Access desktop databases do not publish to the web. Use a web framework and a server database for that.
  • Heavy security. Access can restrict what a front-end exposes, but anyone who can open the file can read the data unless you encrypt it. See the administration chapter for details.

When you outgrow these limits, a common path is to keep your Access forms and reports as a front end and move the tables to a server such as SQL Server. That is covered later in this tutorial.

Access, Excel and SQL Server compared

Question Excel Access SQL Server
Best for Calculations, charts, one-off lists Small multi-table applications Large shared company data
Data rules Few Types, validation, relationships Full, plus programmable logic
Users at once One or a few A few to a few dozen Hundreds and more
Cost of getting started Low Low Higher
Needs a server No No Yes

A good rule: if you find yourself copying the same information into many rows, or if two people must edit the same data at once, you want a database. If you mostly add up numbers, you want a spreadsheet.

A quick look at how Access thinks

Every Access task follows the same pipeline. Data goes into tables. Queries shape that data into the exact rows you need. Forms and reports present those query results to people. Macros and VBA add behavior along the way.

SQL Table, then query

Press Run (or Ctrl + Enter) to see the result here.

In Access you would build this query by dragging fields into the query grid and typing 0 (or No) under the Done criteria. The result is the same list, and a form or report can then be based on that query instead of the raw table.

Learn the vocabulary first: table, field, record, query, form, report. Nearly every Access tutorial, error message and forum answer uses these words.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main advantage of a relational database over a flat spreadsheet?

    Linking tables lets you store a customer's address once and refer to it from many orders, which avoids inconsistent duplicates.
  2. 2. Which Access object is used to print a formatted summary of your data?

    Reports are designed for printing or exporting formatted, grouped data. Forms are for entering and viewing records on screen.
  3. 3. What is the size limit of a single .accdb file?

    An Access database file, including system objects, is limited to 2 GB. Larger data needs linked server tables or a different database.
  4. 4. In database terms, what is a field?

    A field is a column that holds one kind of fact. A row is called a record.

Summary

  • Access is a Windows desktop relational database that stores everything in a single .accdb file.
  • The main objects are tables, queries, forms, reports, macros and modules.
  • Relational design stores each fact once and links tables with keys.
  • Access suits small, multi-table applications with a modest number of users; it does not run on Mac or in a browser, and files are limited to 2 GB.
  • You can keep Access forms and reports as a front end and move tables to SQL Server when you outgrow it.

Next: The Access Interface and Navigation