Managing Linked Tables in Microsoft Access

Linked tables are one of Access's most powerful features — and one of the most misunderstood. A linked table is not a copy of data; it is a live connection to data stored elsewhere. Understanding how to create, manage, and troubleshoot linked tables is essential for any serious Access developer.

What Is a Linked Table?

When you link to an external data source, Access creates a table object in your Navigation Pane that looks and behaves like a regular table — but the data actually lives somewhere else. Every time you open the linked table, Access reads the current data from the source. Changes you make are written back to the source immediately.

Linked tables can point to:

• Another Access database (the backend in a split database)
• An Excel workbook
• A SQL Server database (via ODBC)
• A SharePoint list
• Any ODBC-compliant data source

Creating a Linked Table

From another Access database:

1. External Data → New Data Source → From Database → Access
2. Browse to the source database
3. Select "Link to the data source by creating a linked table"
4. Choose the tables to link

From Excel:

1. External Data → New Data Source → From File → Excel
2. Browse to the workbook
3. Select "Link to the data source"
4. Choose the worksheet or named range

From SQL Server (ODBC):

1. External Data → New Data Source → From Other Sources → ODBC Database
2. Select "Link to the data source"
3. Choose or create a DSN pointing to your SQL Server
4. Select the tables to link

Linked tables appear in the Navigation Pane with an arrow icon indicating they are linked, not local.

The Linked Table Manager

The Linked Table Manager is your central tool for managing all linked tables in a database. Access it via External Data → Linked Table Manager (or Database Tools → Linked Table Manager in older versions).

The manager shows every linked table with its current source path. From here you can:

Refresh a link — updates the link if the source structure has changed (new columns, renamed fields). Select the table and click Refresh.

Relink to a new location — if the source file has moved, select the table, click Relink, and browse to the new location. This is essential after moving a backend database to a new server or folder.

Check all links — select all tables and click Refresh to verify all connections are working.

Automatic Relink on Open

For split databases where the backend might move, add code to the main menu's On Open event to automatically find and relink the backend:

Private Sub Form_Open(Cancel As Integer)
    Dim strBackend As String
    Dim strCurrentPath As String
    
    ' Check if the backend is accessible
    strCurrentPath = CurrentDb.TableDefs("tblCustomers").Connect
    
    ' Extract the path from the connection string
    ' Connection string format: ;DATABASE=C:\path\to\backend.accdb
    strBackend = Mid(strCurrentPath, InStr(strCurrentPath, "DATABASE=") + 9)
    
    If Dir(strBackend) = "" Then
        ' Backend not found - prompt user to locate it
        Dim fd As FileDialog
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        fd.Title = "Locate the database backend file"
        fd.Filters.Add "Access Database", "*.accdb"
        If fd.Show Then
            RelinkTables fd.SelectedItems(1)
        End If
    End If
End Sub

Troubleshooting Linked Table Errors

"Could not find file" or "File not found" The source file has moved or been renamed. Open the Linked Table Manager and relink to the new location.

"ODBC — connection to [server] failed" The SQL Server connection is down. Check network connectivity, server status, and ODBC DSN configuration.

"The Microsoft Access database engine cannot find the input table or query" The linked table name in Access no longer matches the table name in the source. This happens when someone renames a table in the backend. Relink and select the renamed table.

"Record is deleted" The linked table's primary key field is missing or has changed in the source. Refresh the link in the Linked Table Manager.

Linked table shows wrong data Access caches some metadata about linked tables. Right-click the linked table in the Navigation Pane and select "Refresh Link" to force a fresh read.

Performance Considerations

Linked tables to remote sources (SQL Server, SharePoint) are slower than local tables because every read and write involves a network round-trip. To improve performance:

• Use queries with WHERE clauses — never open a linked table directly if it has millions of rows; always filter
• **Avoid SELECT *** — specify only the columns you need
• Cache frequently-used lookup data — import small, rarely-changing lookup tables locally instead of linking them
• Use pass-through queries for SQL Server — these send SQL directly to the server for execution, bypassing Access's query processor

Linked Tables vs. Imported Tables

	Linked	Imported
Data location	Source file	Local Access database
Reflects source changes	Yes (live)	No (snapshot)
Can edit data	Yes (writes back)	Yes (local only)
Performance	Slower (network)	Faster (local)
Use when	Multi-user, live data	Analysis, offline work

Conclusion

Linked tables are the backbone of the split database architecture and the key to connecting Access to enterprise data sources. Master the Linked Table Manager, understand the connection string format, and build automatic relink logic into your applications — and you will handle linked table management with confidence in any environment.