Linking to external data sources and creating solutions based on different sets of data is a strength of Office Access. The Linked Table Manager is a central place to view and manage all data sources and linked tables in an Access database. From time to time, you may need to refresh, relink, find, edit, or delete linked tables because the data source location, table name, or table schema has changed. For example, if you're switching from a test environment to a production environment, you need to change the data source location. If your solution requirements change, you can also add or delete linked tables.
Note
Depending on the version of Access that you have, the Linked Table Manager dialog box works differently. In Microsoft 365, you can use this dialog box to refresh, relink, add, edit, search, and delete linked tables, but you can't export information to Excel. In Access 2016 and later, the dialog box is much simpler, but you can export information to Excel.
Maintain each data source and its linked tables with the Linked Table Manager
- Before you begin
- Refresh a data source and its linked tables
- Relink a data source or linked table
- Find a linked table
- Edit a data source
- Add a data source and linked tables
- Delete a data source or linked table
Before you begin
There are several ways to use the Linked Table Manager, but we recommend this sequence as a best practice:
- Refresh the data source to ensure successful links and to identify problems.
- If there is a problem with the data source, enter the correct location when prompted or edit the data source to fix problems.
- Relink individual tables that have a Failed status. Access automatically refreshes the status to indicate success or failure.
- Repeat steps 1 to 3 until all problems are fixed.
Refresh a data source and its linked tables
Refresh a data source and its linked tables to ensure that the data source is accessible and the linked tables are working properly.
Select External Data > Linked Table Manager. Tip: To refresh a specific linked table from the Navigation Pane, right-click it, and then select Refresh Link. You can also hover over the linked table name to see the connection string and other information.
In the Linked Table Manager dialog box, select a data source or individual linked tables. Selecting a data source selects all its linked tables. Expand (
+) entries in Data Source to select individual linked tables.Select Refresh.
If there's a problem with the data source location, enter the correct location if you're prompted, or edit the data source.
Make sure the Status column is visible (you may have to scroll sideways), and then check it to see the results:
- Success The linked tables have been successfully refreshed.
- Failed One or more of the linked tables has a problem. The most common reasons for a failed status include new credentials or a change to the table name. To fix the problem, relink the data source or linked table.
Select Refresh again until you fix each failed linked table and the Status column displays Success.
Relink a data source or linked table
Relink a data source to change its location and a linked table to change its name.
Select External Data > Linked Table Manager. Tip: In the navigation bar, you can hover over the linked table name to see the connection string and other information.
In the Linked Table Manager dialog box, select a data source or individual linked tables. You may need to expand (
+) entries in the Data Source column.Select Relink.
Whether or not Access locates the data source, it prompts you for a new data source location. After you enter the correct location, you can keep the existing linked table names or choose new table names.
Make sure the Status column is visible (you may have to scroll sideways), and then check it to see the results:
- Success The linked tables have been successfully relinked.
- Failed One or more of the linked tables has a problem.
You're prompted for a new table name. In some cases, you may need to choose a new table from a list of tables. If you're prompted for a connection string, enter the string in the Connection string box. Access automatically refreshes the status to indicate success or failure.
Select Relink again until you fix each failed linked table and the Status column displays Success.
Find a linked table
If you have many linked tables, use the Search box at the top of the Linked Table Manager dialog box to find what you want.
- Enter text in the Search box. Access searches the Data Source Name and Data Source Information columns to find a match. Search isn't case-sensitive and uses type-ahead to list matching linked table names.
- To reset the display, clear the Search box.
Edit a data source
Depending on the data source, you can change the display name, data source path, file name, password, or connection string.
- Select External Data > Linked Table Manager.
Tip
In the navigation bar, you can hover over the linked table name to see the connection string and other information.
In the Linked Table Manager dialog box, select the data source, hover over the data source, and then select Edit.
Change the information in the Edit Link dialog box.
Editing information about an Excel data source
Select Finish.
Add a data source and linked tables
You can add the following types of data sources: Access, SQL (Server and Azure), Excel, or Custom (text, Microsoft Dynamics, SharePoint list, ODBC).
Tip
If you enter connection strings in VBA code, consider adding and editing the connection string from the Linked Table Manager dialog box instead of revising your code directly or writing complex code that changes connection strings automatically.
Select External Data > Linked Table Manager.
In the Linked Table Manager dialog box, select Add.
To make the name easier to find and understand, enter a name in the Display name box. The default display name is the data source type. This name appears in the Data Source column. You can sort this column, and you can use the name to categorize data sources, which is especially helpful when you have many data sources.
Do one of the following:
- Select SQL (Server/Azure), select Next, and then enter the SQL Server sign-in and DSN information. For more information, see Import or link to data in an SQL Server database and Link to or import data from an Azure SQL Server Database.
- Select Access, select Next, and then enter the file name and password in the Add New Link dialog box. For more information, see Import or link to data in another Access database.
- Select Excel, and then enter the file name in the Add New Link dialog box. For more information, see Import or link to data in an Excel workbook.
- Select Custom (Text, Dynamics, SharePoint List, ODBC), and then enter the data source path and connection string in the Add New Link dialog box. For more information, see Connection string syntax.
Select Close.
Delete a data source or linked table
You may want to delete a data source or a linked table because it's no longer needed or because you want to unclutter the Linked Table Manager dialog box.
- Select External Data > Linked Table Manager.
- In the Linked Table Manager dialog box, select one or more data sources or linked tables.
You may need to expand (
+) entries in the Data Source column. - Select Delete.
- When you are prompted to confirm, select Yes. Deleting a linked table only removes the information used to open the table in the data source and not the table itself.
See Also
Export linked data source information to Excel
Introduction to importing, linking, and exporting data in Access