> ## Content Index > Fetch the complete content index at: https://nolongerset.com/llms.txt > Use this file to discover other available public pages before exploring further. # Access 101: Local vs. Linked Tables - URL: https://nolongerset.com/local-vs-linked-tables/ - Published: 2023-10-17T15:08:05.000Z - Updated: 2026-05-08T12:36:17.000Z - Description: 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. - Author: Mike Wolfe - Tags: Access 101, #Import 2026-05-20 02:59 *This article is part of my Quick Start Guide to Microsoft Access series, [Access 101](https://nolongerset.com/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](https://nolongerset.com/front-end-vs-back-end/). - **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](https://nolongerset.com/temp-table-recordsources/) 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 - Allen Browne: [Split your Access database into data and application](http://allenbrowne.com/ser-01.html) [Field Comments on Linked Access Tables in MS AccessIn part 1 of this series on field comments, we discuss maintaining column descriptions on tables linked to MS Access back-end files.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/05/Field-Comments-Linked-Access-Tables-1-.jpg)](https://nolongerset.com/field-comments-on-linked-access-tables/) [Field Comments on Linked TablesIn 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.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/06/Field-Comments-Linked-Tables.jpg)](https://nolongerset.com/field-comments-on-linked-tables/) [3 Free Options for Managing Linked Tables in AccessThe original linked table manager was a terrible tool. Let’s look at three better options for today’s Microsoft Access developers.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/05/Linked-Table-Managers.jpg)](https://nolongerset.com/linked-table-managers/) [Listing External Table SourcesUsing the Parse() function to list linked tables by source. Use this when you want to refer to Linked Table Manager info while writing code.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/11/bistro-498504_1920.jpg)](https://nolongerset.com/listing-external-table-sources/) ##### Acknowledgements - Portions of this article's body generated with the help of [ChatGPT](https://nolongerset.com/chatgpt-nls/)