What Is Microsoft Access?

What Is Microsoft Access?

Access is part of the Microsoft Office / Microsoft 365 family. It wraps a relational database engine in a graphical interface and a set of development tools, and is usually distributed with the Professional editions of Office or as a standalone application.

Unlike classic client-server databases, Access is a file-based system that stores its data in a single file. That file is more than a pile of tables: tables, queries, forms, reports, macros, and code modules all live inside it. This makes an application easy to copy and move, but it also imposes clear scalability limits.

A Short History: From Jet to the ACE Engine

In its early days, Access managed data through Microsoft's Jet (Joint Engine Technology) database engine. Jet was an embeddable engine used not only by Access but by some other Microsoft products of the era.

Over time Microsoft evolved the engine into the Access Database Engine (ACE). ACE was positioned as a successor that added new data types and file-format support on top of Jet. Current versions of Access use the ACE engine by default.

How Access Works: The Core Objects

An Access database is organized around six complementary object types:

• Tables: where data lives as rows and columns. In the relational model, tables link to one another through primary and foreign keys.
• Queries: SELECT and action queries used to filter, join, update, or summarize data. They can be built visually (query-by-example) or written directly in SQL.
• Forms: on-screen interfaces where users enter and edit data, complete with buttons, drop-downs, and subforms.
• Reports: grouped, formatted output designed for printing or export to PDF.
• Macros: lists of actions that let you define simple automation without writing code.
• Modules: units of VBA (Visual Basic for Applications) code that hold more complex business logic.

Because all of these objects sit in the same file, Access becomes a fast way to prototype and deploy small internal applications.

File Formats: .mdb and .accdb

Access has used two main file formats. The older .mdb format is tied to the Jet engine, while the current .accdb format is built on ACE. The .accdb format introduced features such as attachment fields, multi-valued fields, and calculated fields.

Access SQL and Queries

Access has its own dialect of SQL. Behind every query you build in the visual designer there is a text you can edit from SQL view. Standard SELECT queries are largely familiar:

SELECT C.Name, C.City, O.Amount
FROM Customers AS C
INNER JOIN Orders AS O
  ON C.CustomerID = O.CustomerID
WHERE C.City = "Istanbul"
ORDER BY O.Amount DESC;

Beyond standard SQL, Access offers some of its own constructs. Crosstab queries, for example, use the TRANSFORM and PIVOT keywords to turn rows into columns:

TRANSFORM Sum(O.Amount) AS TotalAmount
SELECT O.Year
FROM Orders AS O
GROUP BY O.Year
PIVOT O.Month;

Forms, Reports, and Automation with VBA

What separates Access from a plain table editor is that you can build an application layer on top of the data. Forms create user screens, reports produce printable output, and macros and VBA tie them together.

On the VBA side, DAO (Data Access Objects) is commonly used for programmatic access to data. The example below walks through the results of a query row by row:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Name FROM Customers WHERE City = 'Istanbul'")

Do While Not rs.EOF
    Debug.Print rs!Name
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

This flexibility lets a single user or a small team ship a working internal tool even with limited coding knowledge, which is where much of Access's appeal comes from.

Access vs. SQL Server vs. SQLite

It is important not to confuse Access with similarly named but differently purposed systems. The table below roughly positions three common options:

If you want to see how a server-based system works, our Microsoft SQL Server article offers a good comparison. For a single-file but embedded alternative, look at SQLite, and for another tool with a similar app-building focus, see FileMaker.

How Access Differs from Excel

Access is often compared to Excel, since both work with tables and belong to the Office family. Their core purposes, however, differ. Excel is a spreadsheet tool, ideal for calculation, analysis, and single-layer lists. Access is a relational database: it links multiple tables through keys, protects data integrity with rules, and avoids writing the same data over and over.

A practical rule of thumb: if the data is mainly there to be calculated and lives in a single list, Excel is enough. If the data consists of several related entities such as customers, orders, and products, and you start running into duplicated records and consistency problems, Access is the better fit.

Security, Backup, and Maintenance

Because Access is file-based, security and continuity depend largely on the file itself and where it is stored. A few basic practices reduce trouble over the long run:

• Regular backups: if the single file becomes corrupted, the whole application can be affected, so automated, versioned backups are essential.
• Compact and repair: Access offers a "Compact & Repair" function to tidy up files that bloat over time.
• Front-end / back-end split: keeping the tables in a separate file makes both sharing and backup easier.
• Access control: for sensitive data, configure file sharing and folder permissions carefully; a database password adds another layer.

When Is Access a Good Choice?

Used for the right job, Access is quite productive. It typically fits scenarios such as:

• Internal tracking, inventory, or record-keeping apps used by a small team.
• Mid-scale work where Excel is no longer enough but a full server database would be premature.
• Rapid prototyping: turning an idea into forms and reports in a short time.
• Acting as a front end that connects to external sources such as SQL Server, ODBC, Excel, or text files.

Where Access Falls Short: Limitations

Access's limits are a natural consequence of its architecture. Consider another solution when you hit these:

• Size: the 2 GB per-file limit is a hard ceiling for growing data sets.
• Concurrency: a file-based architecture is not designed for many users writing heavily at the same time; performance is best with a small number of concurrent users.
• Web and high traffic: it is not suited to be the backend of public, high-traffic web applications.
• Platform: Access is primarily aimed at the Windows desktop environment.

Front End / Back End and Upsizing to SQL Server

As Access applications grow, a common pattern is to split the database in two: a back-end file that holds the tables, and a front-end file, distributed to users, that holds the forms and reports. This separation makes maintenance and updates easier in multi-user scenarios.

The next step is often moving the data to a server database such as SQL Server, a process usually called "upsizing." In this scenario the tables move to the server, while Access can remain a front end through linked tables and pass-through queries.

Licensing and the Access Runtime

Access is a commercial Microsoft product, generally offered as part of Microsoft 365 subscriptions or the Professional editions of Office. There is also a free Access Runtime component that lets you distribute an application you built to users who do not have the full version of Access installed; with it, users can run the application but cannot access the design tools.

Summary

Microsoft Access is a practical way to build a small-scale, single-file database application that ships with its own interface. It is powerful for small teams, internal tools, and prototypes, but when size, concurrency, or web scale come into play, it should hand off to server-based systems. Used for the right job it is fast; used for the wrong one, its limits are felt quickly.