Import or link to data in another Access database

Applies To
Access for Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

You can bring data from one Access database into another in several ways. Copying and pasting is the simplest method, but importing and linking give you better control over the data that you bring into the destination database and how you bring it in.

This article explains how to import or link to data in another Access database.

What do you want to do?

Understand importing and linking to data from another Access database

When you import from another database, Access creates a copy of the data in the destination database without changing the source. During the import operation, you can choose the objects that you want to copy, control how tables and queries are imported, and specify whether relationships between tables should be imported.

You might want to import data to create tables that are similar to tables in another database. You can copy the entire table or just the table definitions to avoid designing each table manually. When you import only the table definition, you get an empty table. In other words, the fields and field properties are copied to the destination database, but not the data in the table. Another advantage of importing, compared to a copy-and-paste operation, is that you can choose to import the relationships between the tables along with the tables themselves.

If your goal is to add records from one database to an existing table in another database, you should consider importing the records to a new table and then creating an append query. You cannot append records to an existing table during an import operation. For more information about append queries, see the article Add records to a table by using an append query.

You might want to link to data in another Access database if your organization uses several Access databases but needs to share data in some tables, such as Employees. Instead of duplicating the table in each database, you can keep the table in one database and link to it from other databases. This also helps when another workgroup or department needs to add to and use the data in your database, but you want to keep control of the table structure.

Import data from another Access database

The process of importing data follows these general steps:

  • Prepare for the import operation
  • Run the Import Wizard
  • Optionally save the import settings as an import specification for later reuse

The following sets of steps explain how to perform each action.

Prepare for the import operation

  1. Locate the source database and identify the objects that you want to import. If the source database is an .mdb or .accdb file, you can import tables, queries, forms, reports, macros, and modules. If the source file is an .mde or .accde file, you can import only tables.

  2. If this is the first time you are importing data from an Access database, refer to the following table for some useful tips.

    Element Description
    Multiple objects You can import multiple objects in a single import operation.
    New object Each import operation creates a new object in the destination database. You cannot overwrite an existing object or append records to an existing table by using an import operation.
    Importing a linked table If the source table (for example, Employees1 in the Sales database) is actually a linked table (a table that links to the Employees table in the Payroll database), the current import operation is replaced by a linking operation. At the end of the operation, you will see a linked table (named, for example, Employees1) that links to the original source table (Employees in the Payroll database).
    Skipping fields and records You cannot skip specific fields or records when importing data from a table or query. However, if you do not want to import any of the records in a table, you can choose to import only the table definition.
    Relationships You can choose to import the relationships between source tables.
    Table definition You can choose to import an entire table or just the table definition. When you import just the definition, Access creates a table that has the same fields as the source table, but no data.
    Lookup fields If a field in the source table looks up values in another table or query, you must import the related table or query if you want the destination field to display lookup values. If you do not import the related table or query, the destination field will only display the lookup IDs.
    Queries You can import a query either as a query or as a table. If you import a query as a query, then you must import the underlying tables.

  3. Close the source database. Make sure that no user has it open in exclusive mode.

  4. Open the destination database. Make sure that the database isn't read-only and that you have the permissions that you need to add objects and data. If the source database is password protected, you are prompted to enter the password each time you use it as a source for an import operation.

    Note

    If you want to import the data into a new database, you must create a blank database that does not contain any tables, forms, or reports before starting the import operation.

    The import operation doesn't overwrite or modify existing tables or objects. If an object with the same name as the source object already exists in the destination database, Access appends a number (1, 2, 3, and so on) to the imported object's name. For example, if you import the Issues table into a database that already has a table named Issues, the imported table is named Issues1. If the name Issues1 is already in use, the new table is named Issues2, and so on. If you want to append the records in the source table to a table in the destination database, you must use an append query instead of running an import operation. For more information about append queries, see Add records to a table by using an append query.

Import the data

  1. The location of the import wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:

    • If you're using Microsoft 365, Access 2021, or Access 2019 on the External Data tab, in the Import & Link group, click New Data Source > From Database > Access.
    • If you're using Access 2016, on the External Data tab, in the Import & Link group, click Access.

  2. The Get External Data - Access Database import and link wizard opens.

    Screenshot of the get External Data - Access Database import wizard.

  3. In the File name box, type the name of the source database or click Browse to open the File Open dialog box.

  4. Select Import tables, queries, forms, reports, macros, and modules into the current database and click OK.

    The Import Objects dialog box opens.

    Select objects to import on the Import Objects dialog box

  5. In the Import Objects dialog box, on the Tables tab, select the tables you want to import. If you want to import queries, click the Queries tab and select the queries you want to import.

    To clear a selected object, click it again.

  6. Click Options to specify additional settings. The following table describes how each option affects the results of the operation.

    Element Description
    Relationships check box Select to import the relationships between the selected tables.
    Menus and Toolbars check box Select to import any custom menus and toolbars that exist in the source database. The menus and toolbars are displayed on a tab named Add-Ins.
    Import/Export Specs check box Select to import any saved import or export specifications that exist in the source database.
    Nav Pane Groups check box Select to import any custom Navigation pane groups that exist in the source database.
    Definition and Data option button Select to import the structure and data of all selected tables.
    Definition Only option button Select to import only the fields in the selected tables. The source records are not imported.
    As Queries option button Select to import the selected queries as queries. In this case, remember to import all the underlying tables along with the queries.
    As Tables option button Select to import queries as tables. In this case, you need not import the underlying tables.

  7. Click OK to finish the operation. Access copies the data and displays error messages if it encounters problems. If the import succeeds, the last page of the wizard lets you save the operation details as an import specification for later use.

Linking lets you connect to data in another database without importing it. This way, you can view and modify the latest data in both the source and destination databases without creating and maintaining two copies of the same data. You can link only to tables in another Access database. You can't link to queries, forms, reports, macros, or modules.

When you link to a table in an Access database, Access creates a new table called a linked table. The linked table maintains a link to the source records and fields. Any changes that you make to the data in the source database are reflected in the linked table in the destination database, and vice versa. However, you can't change the structure of a linked table in the destination database. In other words, you can't add or delete a field or modify a field's data type.

The process of linking to data in another Access database follows these general steps:

  • Prepare for the link operation
  • Run the Link Wizard

The following sets of steps explain how to perform each action.

  1. Locate the source database. The file format can be MDB, MDE, ACCDB, or ACCDE. If the source database is password protected, you're prompted to enter the password during the linking operation.

  2. Identify the tables that you want to link to. You can link to tables, but you can't link to queries, forms, reports, macros, or modules. If this is your first time linking to tables in another Access database, refer to the following table for some useful tips.

    Element Description
    Multiple objects You can create links to multiple tables in a single linking operation. A linked table is created for each source table.
    Linked tables as source You cannot link to a table that is already a linked table in the source database.
    For example, if the Employees1 table that you want to link to in the Sales database is actually a linked table that links to the Employees table in the Payroll database, you cannot use the Employees1 table in the Sales database as the source table. You should instead link directly to the Employees table in the Payroll database.
    New or existing table Each linking operation creates a new linked table for each source table. You cannot overwrite or append to an existing table by using a linking operation.
    Relationships If you select multiple source tables, the relationships between the tables are automatically carried forward to the destination database. However, you cannot change or delete the relationship in the destination database.
    Lookup fields If a field in the source table looks up values in another table, you must remember to link to the related table if you want the destination field to display lookup values. If you do not link to the related table or query, the destination field only displays the lookup IDs.

  3. Close the source database. Make sure that no other user has the database open in exclusive mode.

  4. Open the destination database. Make sure that the destination database isn't read-only and that you have the permissions that you need to add objects and data.

    Note

    If you want to create the links in a new database, you must create a blank database (one that does not contain any tables, forms, or reports) before starting the link operation.

    The operation doesn't overwrite or modify existing tables or objects. If an object with the same name as the source object already exists in the destination database, Access appends a number (1, 2, 3, and so on) to the linked table's name. For example, if you link to the Issues table from a database that already has a table named Issues, the linked table is named Issues1. If the name Issues1 is already in use, the new table is named Issues2, and so on. Remember that if you want to append the records in the source table to a table in the destination database, you must use an append query instead of running a linking operation. For more information about append queries, see Add records to a table by using an append query.

  1. The location of the link wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:

    • If you're using Microsoft 365, Access 2021, or Access 2019 on the External Data tab, in the Import & Link group, click New Data Source > From Database > Access.
    • If you're using Access 2016, on the External Data tab, in the Import & Link group, click Access.

  2. The Get External Data - Access Database import and link wizard opens.

    Screenshot of the get External Data - Access Database import wizard.

  3. In the File name box, type the name of the source database or click Browse to open the File Open dialog box.

  4. Click Link to the data source by creating a linked table, and then click OK. The Link Tables dialog box opens.

    Select a table to link to on the Link Tables dialog box

  5. In the Link Tables dialog box, select the tables you want to link to. To clear a selection, click the table again.

  6. Click OK to finish the operation. Access creates the linked tables.

  7. Open the linked tables in Datasheet view to ensure that the data looks correct.

What else should I know?

Top of Page