Import or link to data in another Access database

You can bring data from one Access database into another in many ways. Copying and pasting is the simplest method, but importing and linking offer you better control and flexibility over the data that you bring, 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

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

Common scenarios for importing data or objects from an Access database

You typically import data for the following reasons:

• You want to merge two databases by copying all the objects in one database to another. When you import, you can copy all the tables, queries, forms, reports, macros, and modules, along with table relationships, to another database in a single operation.

• You need to create some tables that are similar to tables that exist in another database. You might want to copy the entire table or just the table definitions to avoid manually designing each of these tables. When you choose to 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-paste operation) is that you can choose to import the relationships between the tables along with the tables themselves.

• You need to copy a set of related objects to another database. For example, you want to copy the Employees table and the Employees form to a second database. Importing enables you to copy an object and all of its related objects to another database in a single operation.

Note: 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 Create an append query.

Common scenarios for linking to a table in an Access database

Consider linking to data under the following conditions:

• Your organization uses several Access databases, but data in some tables, such as Employees, need to be shared between various databases. Instead of duplicating the table in each such database, you can keep the table in a single database and link to it from other databases.

• Another workgroup or department needs to be able to add to and use the data in your database, but you want to continue to own the structure of the tables.

Special considerations for importing from Access 2.0 and Access 95 databases

You can link tables from any Access database into Access 2007, regardless of the file format of the source database. However, there are certain restrictions on the types of objects you can import from Access 2.0 or Access 95 databases.

Access 2.0    You can import tables, queries and macros from an Access 2.0 database (.mdb) into an Access 2007 database (.accdb). However, you cannot import forms, reports, or modules from an Access 2.0 database.

Access 95    You can import tables, queries and macros from an Access 95 database (.mdb) into an Access 2007 database (.accdb). If the Vbacv20.dll file is installed on your computer, you can also import forms, reports and modules from an Access 95 database. The Vbacv20.dll file is installed by some earlier versions of Microsoft Office. Microsoft does not provide this file with Access 2007.

If the Vbacv20.dll file is not installed on your computer, you cannot import forms, reports and modules from an Access 95 database. In that case, if your Access 95 database contains forms, reports, and modules that you cannot manually convert and re-create in Access 2007, you should use an earlier version of Access, such as Microsoft Office Access 2003, to convert the file to the Access 2000 or Access 2002-2003 file format.

The following table summarizes which importing and linking capabilities are supported between Access 2007 and databases created in earlier versions of Access:

Source file format	Importing tables, queries, and macros into Access 2007	Importing forms, reports, and modules into Access 2007	Linking to tables
Access 2.0	Supported	Not supported	Supported
Access 95	Supported	Supported, if the Vbacv20.dll file is installed on your computer	Supported
Access 97 through Access 2003	Supported	Supported	Supported

For more information about importing from Access 2.0 and Access 95 databases, click the links in the See Also section.

The steps in the following sections explain how to import and link to database objects.

Top of Page

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	Descrip tion
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.
Record source objects	Importing a query, form, or report does not automatically import the underlying record sources. You must import all the underlying record sources, or else the query, form, or report will not work.
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.
Subforms, subreports	When you import a form or report, subforms and subreports included in the form or report are not automatically imported. You need to import each subform, subreport, and their underlying record sources for the form or report to work in the destination database.
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.

1. Close the source database. Ensure that no user has it open in exclusive mode.

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

   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. Click the Microsoft Office Button , and then click New.

   The import operation does not overwrite or modify 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 important to note 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 an import operation. For more information about append queries, see the article Create an append query.

Import the data

1. On the External Data tab, in the Import group, click Access.

   Note: The External Data tab is available only when a database is open.

2. In the File name text box, type the name of the source database or click Browse to display the File Open dialog box.

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

4. In the Import Objects dialog box, click each tab and select the objects you want.

   To cancel a selected object, click the object again.

5. Click Options to specify additional settings.

   The following table describes how each option impacts 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.

1. Click OK to finish the operation.

   Access copies the data and displays error messages if it encounters any problems. If the operation succeeds in importing the data, the final page of the wizard allows you to save the details of the operation as an import specification for future use. The next steps explain how to save the operation details as a specification.

Save the import specification

1. Click Save import steps to save the details of the operation.

   Saving the details helps you repeat the operation at a later time without having to step through the wizard each time.

2. In the Save as box, type a name for the import specification. Optionally, type a description in the Description box.

3. Select the Create Outlook Task check box if you want to perform the operation at fixed intervals, such as weekly or monthly.

   This creates a Microsoft Office Outlook 2007 task that lets you run the specification.

4. Click Save Import.

   If you did not select the Create Outlook Task check box, Access saves the import specification and imports the objects you specified. You can now review the imported tables and other objects to ensure that they were imported correctly.

If you selected the Create Outlook Task check box, Access starts Outlook. Use this procedure to complete the steps that follow.

Note: If Outlook is not installed, Access displays an error message. If Outlook is not configured properly, the Outlook Startup Wizard starts. Follow the instructions in the wizard to configure Outlook.

1. In Outlook, review and modify the task settings, such as Due date and Reminder.

   To make the task recur, click Recurrence. This figure shows the task scheduler with some typical settings:

   

   For information about scheduling Outlook tasks, see the article Schedule an import or export operation.

2. Click Save and Close.

   To run the specification at a later date, open the task in Outlook and on the Task tab, in the Microsoft Office Access group, click Run Import .

3. In Access, review the imported tables and other objects to ensure that all the data and objects were copied.

Top of Page

Link to data in another Access database

Linking lets you 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 creating and maintaining two copies of the same data. You can link only to tables in another Access database. You cannot 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, which maintains a link to the source records and fields. Any changes you make to the data in the source database are reflected in the linked table in the destination database, and vice versa. However, you cannot change the structure of a linked table in the destination database. In other words, you cannot make changes to a linked table such as adding or deleting a field, or modifying the data type of a field.

Prepare to link tables in an Access database

1. Locate the source database.

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

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

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.

1. Close the source database. Ensure that no other user has the database open in exclusive mode.

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

   Click the Microsoft Office Button , and then click New.

   The operation does not overwrite or modify 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 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 Create an append query.

Link to the data

1. On the External Data tab, in the Import group, click Access.

   Note: The External Data tab is available only when a database is open.

2. In the File name text box, type the name of the source database or click Browse to display the File Open dialog box.

3. Click Link to the data source by creating a linked table, and then click OK.

4. In the Link Tables dialog box, select the tables you want to link to.

   To cancel a selection, click the table again.

5. Click OK to finish the operation.

   Access creates the linked tables.

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

Top of Page