Manage linked tables

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 location for viewing and managing all data sources and linked tables in an Access database. From time-to-time, you may need to refresh, relink, edit, or delete linked tables because of changes to the data source location, table name, or table schema. For example, you are switching from a test to a production environment, so you need to change the data source location. If your solution requirements have changed, you can also add or delete linked tables.

Linked Table Manager dialog box

Maintain each data source and its linked tables with the Linked Table Manager

What do you want to do?
Before you begin
Refresh a data source and its linked tables
Relink a data source or 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 approaches to using the Linked Table Manager, but we recommend the following sequence of steps as a best practice:

  1. Refresh the data source to ensure successful links and to identify problems.

  2. If there is a problem with the data source, enter the correct location when prompted or edit the data source to fix problems.

  3. Relink individual tables that have a Failed status. Access automatically refreshes the status to indicate success or failure.

  4. Repeat steps 1 to 3 until all problems are fixed.

Top of Page

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.

  1. 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 connection string and other information.

  2. In the Linked Table Manager dialog box, select a data source or individual linked tables.

    Selecting a data source selects all of its linked tables. Expand (+) entries in the Data Source to select individual linked tables.

  3. Select Refresh.

  4. If there is a problem with the data source location, enter the correct location if you are prompted or Edit the data source.

  5. 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.

  6. Select Refresh again until you have fixed each failed linked table and the Status column displays "Success".

Top of Page

Relink a data source or linked table

Relink a data source to change its location and a linked table to change its name.

  1. Select External Data > Linked Table Manager.

    Tip    In the navigation bar, you can hover over the linked table name to see connection string and other information.

  2. 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.

  3. Select Relink.

  4. Whether or not Access locates the data source, Access prompts you for a new data source location. After you enter the correct location, you can either keep the existing linked table names unchanged or choose new table names.

  5. 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.

  6. You are prompted for a new table name. In some cases, you may need to choose a new table from a list of tables. If you are prompted for a connection string, enter the string in the Connection string box. Access automatically refreshes the status to indicate success or failure.

  7. Select Relink again until you have fixed each failed linked table and the Status column displays "Success".

Top of Page

Edit a data source

Depending on the data source, you can change the display name, data source path, file name, password, or connection string.

  1. Select External Data > Linked Table Manager.

    Tip    In the navigation bar, you can hover over the linked table name to see connection string and other information.

  2. In the Linked Table Manager dialog box, select the data source, hover over the data source, and then select Edit.

  3. Change the information in the Edit Link dialog box.

    Edit Link dialog box for an Excel data source

    Editing information about an Excel data source

  4. Select Finish.

Top of Page

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, ShaePoint list, ODBC).

Tip    For Access developers who 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 automatically changes connection strings.

  1. Select External Data > Linked Table Manager.

  2. In the Linked Table Manager dialog box, select Add.

  3. To improve discoverability and meaning, enter a name in the Display name box. The default display name is the type of data source.

    This name displays in the Data Source column, you can sort this column, and you can use the name to categorize data sources, which is especially useful when you have many data sources.

  4. Do one of the following:

  5. Select Close.

Top of Page

Delete a data source or linked table

You may want to delete a data source or a linked table because they are no longer needed and to unclutter the Link Table Manager dialog box.

  1. Select External Data > Linked Table Manager.

  2. 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.

  3. Select Delete.

  4. 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.

Top of Page

See Also

Export linked data source information to Excel

Introduction to importing, linking, and exporting data in Access

Administer ODBC data sources

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×