Local vs. Linked Tables in Microsoft Access
An introduction to the differences between local and linked tables in Microsoft Access, including five situations where local tables are the better choice.
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
Mike Wolfe
Mike Wolfe
Mike Wolfe
Acknowledgements
- Portions of this article's body generated with the help of ChatGPT
- Initial draft generated with the help of ChatGPT
- Cover image created with Microsoft Designer
