How to use the Linked Table Manager in Access 2016

You can use the Linked Table Manager to update your linked tables with any structural changes from the source table. You can also use it to export a list of linked tables to Excel.

The Linked Table Manager allows you to manage any linked table that you might have in the Access database. This could include tables that are linked as part of a split Access database, or tables that are linked to another source.

One of the main purposes of the Linked Table Manager is to help us update the linked tables when there's a change to the source database that impacts on our linked tables.

You can use the Linked Table Manager when the source is an Access database that has been moved to a different location. You can also use it when the source table definition changes in SQL Server or another system.

Structural Change to a SQL Server Table

If you have a linked table to a SQL Server database, and there's a structural change to that table at the SQL Server end (for example, a new column is added), you will need to update your linked table to reflect the change.

  1. Screenshot of SQL Server with the new column

    New Column in SQL Server

    In this example, a new column (called Biography) has been added to the Artists table in SQL Server.

    So in the next step, we'll switch over to the Access database and update the linked tables.

  2. Screenshot of the Linked Table Manager option

    Open the Linked Table Manager in Access

    This table was opened after the column had been added in SQL Server, but as you can see, the new column is nowhere to be seen.

    We'll fix that now.

    Right-click on a linked table and select Linked Table Manager.

    You can also use the Linked Table Manager button in the Ribbon (from the External Data tab.

  3. Screenshot of the Linked Table Manager

    Select Table/s to be Updated

    Select the table/s that need to be updated and click OK.

  4. Screenshot of the success message

    Close the Success Message

    Once the linked tables have been updated a success message will appear.

    Click OK to close the message.

  5. Screenshot of the Linked Table Manager

    Close the Linked Table Manager

    Click Close to close the Linked Table Manager.

  6. Screenshot of the table in Design View with the new column

    Check that it Worked

    If the linked table was already open, close it and reopen it.

    The change should now be reflected in the linked table.

The Backend Access Database has Moved?

You can also use the Linked Table Manager when the source database is moved.

For example, if you have tables linked to a backend Access database, and you move that backend database, you will need to update those linked tables in the frontend database to point to the new location.

To fix this, use the Linked Table Manager the same way as in the above example. The only difference is an extra step is required (to navigate to the new file location of the source database).

See Update Linked Tables after the Backend Changes Location for a step-by-step guide.

Export a list of Linked Tables to Excel

If you would like to export a list of your linked tables to Excel, you can do this via the Linked Table Manager.

Simply use the Export To Excel button on the Linked Table Manager.

See Export a list of Linked Tables to Excel for detailed steps.