Access 101: Local vs. Linked Tables

In Access, tables can be local (stored in the front-end file) or linked (stored in a separate database). Let's explore when and why to use each type.

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.

Additional Reading

Field Comments on Linked Access Tables in MS Access
In part 1 of this series on field comments, we discuss maintaining column descriptions on tables linked to MS Access back-end files.
Field Comments on Linked Tables
In this 6-part series, I cover everything you need to know about field descriptions for linked tables with Access and SQL Server back-end databases.
3 Free Options for Managing Linked Tables in Access
The original linked table manager was a terrible tool. Let’s look at three better options for today’s Microsoft Access developers.
Listing External Table Sources
Using the Parse() function to list linked tables by source. Use this when you want to refer to Linked Table Manager info while writing code.

  • Portions of this article's body generated with the help of ChatGPT

All original code samples by Mike Wolfe are licensed under CC BY 4.0