Access 101: Local vs. Linked Tables

This article is part of my Quick Start Guide to Microsoft Access series, Access 101.

When building an Access database, one of the most important distinctions to understand is between local and linked tables. This mainly applies to “front-end” Access files that connect to a “back-end” database.

Local Tables: tables that physically reside in the Access file
Linked Tables: tables that physically reside in another location (e.g., a back-end Access file, SQL Server, or MySQL), but can otherwise be treated like local tables (e.g., data in linked tables can be created, read, updated, and deleted)

When to Use Each Type

Generally speaking:

Front-end Access files have (mostly) linked tables
Back-end Access files have local tables
The linked tables in the Access front-end point to the tables in the back-end

While front-end files mostly contain linked tables, it's common to have a small number of local tables, too. Here is some additional information about when and why to use local versus linked tables in the context of a front-end Access file.

Local Tables

Any changes the end-user makes to data stored in local tables will be lost when the front-end file is updated or replaced, so user-editable data should never be stored in local tables.
Use local tables temporarily during development for tables whose structure changes frequently. Just remember to link these tables to the back-end before deployment.
Local tables can be used as record sources for reports to improve performance when a report is based on a complex query. If you do that, be sure to clear the data or delete the local table and then compact the database to avoid file bloat. (This is an advanced technique.)

Linked Tables

Any tables containing data that users can edit should be linked. This includes main data tables and editable lookup tables.
Read-only lookup tables that users cannot edit can be stored as local tables. But linked lookup tables allow enforcing referential integrity with related tables.
The front-end can link to multiple back-end databases. But related tables should be in the same back-end for referential integrity.
You cannot change the structure of linked tables from the front-end. This must be done directly in the back-end database.

A Quick Note About "Referential Integrity"

Referential integrity is a database concept that ensures consistency between related data across multiple tables.

It involves creating relationships between tables that have foreign keys and primary keys. The foreign key in one table refers to the primary key of another table. Referential integrity prevents users from entering inconsistent data across these related tables, such as creating a record in one table that refers to a non-existent record in another table.

Maintaining referential integrity is crucial for ensuring the accuracy and validity of a relational database.

This topic will be covered in greater depth in a future article in this series.