Import or link to data in another Access database

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010.

You can combine data from one Access database into another in several ways. Copying and pasting is the easiest method, but importing and linking provide you greater authority and freedom over the data that you import, and over how you bring that data into the destination database.

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

Import data from another Access database

Link to data in another Access database

Understand importing and linking to data from another Access database

Once you import from another database, Access formulates a copy of the data in the destination database without amending the source. During the import operation, you can select the objects you want to copy, control how tables and queries are imported, specify whether relationships between tables should be imported, and so on.

You may want to import data for example to create some tables that are similar to tables that exist in another database. You could want to copy the whole table or merely the table definitions to avoid manually creating each of these tables. After you pick to import only the table definition, you receive an empty table. Expressed simply, 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-paste operation) is that you can choose to import the relationships between the tables along with the tables themselves.

If your goal is to insert records from one database to an existing table in another database, you should consider importing the records to a new table and then designing an append query. You cannot append records to an current 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 organisation uses several Access databases, but data in some tables, such as Employees, need to be shared between various databases. Rather than copying the table in each such database, you can preserve the table in a single database and link to it from other databases. Another workgroup or department must be able to add to and use the data in your database, but you want to continue to own the structure of the tables.

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 conduct each action.

Prepare for the import operation

  1. Find 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 solely tables.
  1. If this is your first experience of importing data from an Access database, review the following table for some useful tips.
ElementDescription
Multiple objectsYou can import multiple objects in a single import operation.
New objectEach 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 tableIf 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 recordsYou 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.
RelationshipsYou can choose to import the relationships between source tables.
Table definitionYou 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 fieldsIf 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.
QueriesYou 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.
  1. Exit the source database. Check that no user has it open in exclusive mode.
  1. Access the destination database. Make sure that the database is not read-only and that you have the necessary permissions to insert objects and data to the database. If the source database is password protected, you are requested to state 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 does not replace or update any of the 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 name of the import object. For example, if you import the Issues table to a database that already has a table named Issues, the imported table will be named Issues1. If the name Issues1 is already in use, the new table will be named Issues2, and so on.

It is crucial to remember that if you want to add the records in the source table to a table in the destination database, you must use an append query rather than running an import operation. For more information about append queries, see the article Add records to a table by using an append query.

Import the data

  1. The location of the import wizard changes minutely based on your version of Access. Select the steps that match your Access version:
  • If you’re using the latest version of the Microsoft 365 subscription version of Access, on the External Data tab, in the Import & Link group, press New Data Source > From Database > Access.
  • If you’re using Access 2016, Access 2013 or Access 2010, on the External Data tab, in the Import & Link group, pick Access.

  1. The Get External Data – Access Database import and link wizard displays.

Screenshot of the get External Data - Access Database import wizard

  1. In the File name text box, input the name of the source database or pick Browse to show the File Open dialogue box.

  1. Press Import tables, queries, forms, reports, macros, and modules into the current database and pick OK. The Import Objects dialogue box appears.

Select objects to import on the Import Objects dialog box

  1. In the Import Objects dialogue box, on the Tables tab, click your desired tables to import. If you want to import queries, select the Queries tab and choose the queries you want to import. To cancel a chosen object, pick the object again.

  1. Press Options to define extra settings. The following table outlines how each option affects the results of the operation.

  1. Select OK to finish the operation. Access duplicates the data and presents error messages if it experiences any problems. If the operation accomplishes importing the data, the final page of the wizard enables you to save the details of the operation as an import specification for future use.

Link to data in another Access database

Linking allows you to connect to data in another database without importing it, so that you can view and modify the latest data in both the source and destination databases without designing and storing two copies of the same data. You can link only to tables in another Access database. You are unable to link to queries, forms, reports, macros, or modules.

Once you link to a table in an Access database, Access produces a new table, called a linked table, which keeps a link to the source records and fields. Any changes you make to the data in the source database are mirrored in the linked table in the destination database, and vice versa. However, you cannot alter the structure of a linked table in the destination database. Put simply, making changes to a linked table such as adding or deleting a field, or modifying the data type of a field is unachievable.

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 run each action.

Prepare to link tables in an Access database

  1. Find the source database. The file format can be MDB, MDE, ACCDB, or ACCDE. If the source database is password protected, you will be asked to type the password during the linking operation.

  1. Identify the tables to which you want to link. You can link to tables, but you cannot link to queries, forms, reports, macros, or modules. If this is the first time you are linking to tables in another Access database, refer to the following table for some useful tips.
ElementDescription
Multiple objectsYou can create links to multiple tables in a single linking operation. A linked table is created for each source table.
Linked tables as sourceYou 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 tableEach 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.
RelationshipsIf 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 fieldsIf 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.

  1. Exit the source database. Check that no other user has the database open in exclusive mode.

  1. Open the destination database. Ensure that the destination database is not read-only and that you have the necessary permissions to add objects and data to the database.

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 avoids overwriting or updating any of the existing tables or objects. If an object with the same name as the source object is established in the destination database, Access adjoins a number (1, 2, 3, and so on) to the name of the linked table. For example, if you link to the Issues table from a database that already has a table named Issues, the linked table will be named Issues1. If the name Issues1 is already in use, the new table will be 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 the article Add records to a table by using an append query.

Link to the data

  1. The location of the link wizard varies moderately based upon your version of Access. Select the steps that reflect your Access version:

  • If you’re using the latest version of the Microsoft 365 subscription version of Access, on the External Data tab, in the Import & Link group, pick New Data Source > From Database > Access.
  • If you’re using Access 2016, Access 2013 or Access 2010, on the External Data tab, in the Import & Link group, select Access.

  1. The Get External Data – Access Database import and link wizard launches.Screenshot of the get External Data - Access Database import wizard

  1. In the File name text box, enter the name of the source database or press Browse to display the File Open dialogue box.

  1. Select Link to the data source by creating a linked table, and then pick OK. The Link Tables dialogue box opens.Select a table to link to on the Link Tables dialog box

  1. In the Link Tables dialogue box, verify the tables you want to link to. To cancel a selection, select the table again.

  1. Press OK to finish the operation. Access designs the linked tables.

  1. Access the linked tables in Datasheet view to guarantee that the data appears correct.

What else should I know?

  • For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks.

Top of Page

Leave a Reply

%d bloggers like this: