Local vs. Linked Tables in Microsoft Access

Mike Wolfe -

In the world of Microsoft Access, tables are the backbone of your database.

They store the raw data that your forms, reports, and queries manipulate. But not all tables are created equal. In Access, there are two types of tables:

  • Local Tables
  • Linked Tables

Understanding the difference between these two types of tables is crucial to building and maintaining Access applications.

Local Tables

A Local Table, as the name suggests, is a table that is stored within the current Access database file (.accdb or .mdb).

The data in a local table is directly managed by Access and is stored in the same physical file as the rest of your database objects, such as forms, reports, and queries.  Local tables are the default type of table in Access. When you create a new table using the Table Design or Table Wizard, you're creating a local table.

User-editable data should never be stored in a front-end local table. This is because changes to that data will be lost when a new version of the front-end gets deployed.

Linked Tables

A Linked Table, on the other hand, is a table where the data is stored outside of the current Access database file.

The table appears in your Access database, but the data is actually stored in another database or another type of data source, such as SQL Server, Oracle, or even another Access database.

When you create a linked table, Access stores only the connection information and the table schema, not the data itself. The data remains in the external source and is fetched as needed.  Additionally, if you delete a linked table you are not deleting the data itself, only the connection information used to connect to the source table.

Most tables in an Access front-end database file should be linked tables.

When to Use Local Tables

Since most tables should be linked, it's instructive to consider those occasional situations where it makes sense to use local tables.

  • Temporary Working Data: Local tables are often used for temporary working data, such as for complex reports that require intermediate processing steps.
  • Static Lookup Tables: Static lookup tables that users cannot edit often make good local tables. These are tables that contain data that doesn't change often, like a list of states or countries.  However, if these lookup tables have relationships with other tables in the back-end, then they should be colocated with those related tables so that referential integrity may be enforced.
  • Structured Global Data: Certain kinds of application-level global data, such as string values to support language localization, can be stored in local tables.
  • Initial Development and Design: Local tables are ideal for the initial creation and design phase of new tables, allowing for easy adjustments directly in the front-end database. Once the design is stable, the table can be moved to the back-end database and linked from the front-end.
  • Single-Row Dummy Table: Microsoft Access does not support "table-less" UNION queries.  The workaround is to use a single-row dummy table in those situations.

Further Reading

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.
No Longer SetMike Wolfe
How Access Reads Data From SQL Server
How does Microsoft Access pull data from SQL Server? You’ll be amazed when we pull back the curtain to expose how Access interacts with SQL Server.
No Longer SetMike Wolfe
Enforcing a Single-Row Table in MS Access and SQL Server
There are many uses for a table with one--and only one--row in it. Using such a table is simpler if you can rely on the sanctity of its one-row-ness.
No Longer SetMike Wolfe
Acknowledgements