Export a database object to another Access database

Access provides a number of ways to copy an object such as a table or form from one database to another. Copying and pasting an object is easiest, but exporting an object offers you more options. For example, you can export the table definition and the data in the table, or export just the table definition (a blank copy of the table). You can also save the details of the operation as an export specification for future use.

This topic explains how to export an object by using a wizard and then how to save the details of that operation as a specification.

What do you want to do?


Understand exporting data and objects to other Access databases

You can export a table, query, form, report, macro, or module from one Access database to another. When you export an object, Access creates a copy of the object in the destination database.

 Note   You cannot export partial objects. For example, you cannot export only the records or fields selected in a view. To copy a portion of an object, copy and paste the data instead of exporting it.

You export database objects when you need to do any of the following tasks:

  • Copy the structure of a table to another database as a shortcut to creating a new table.
  • Copy the design and layout of a form or report to another database as a quick way to create a new form or report.
  • Copy the latest version of a table or form to another database at regular intervals. To do this, you can create an export specification the first time you export the object, and then use the specification to repeat the operation later.

Note that exporting an object to another database differs very little from opening a second database and then importing the object from the first. The two main differences between importing and exporting objects between Access databases are:

  • You can import multiple objects in a single operation, but you cannot export multiple objects in a single operation. If you want to export multiple objects to another database, it is easier to open the destination database and then perform an import operation from within that database.
  • In addition to database objects, you can import relationships between tables, plus any import and export specifications, and menu bars and toolbars. You can also import a query as a table. Exporting does not offer you these options.

Top of Page Top of Page

Export a database object to another Access database

The steps in this section explain how to prepare for an export operation, run the export wizard, and then save and run the export specification.

Prepare for the export

  1. Open the source database, if it is not already open. The file format can be either MDB or ACCDB. If the file is in MDE or ACCDE format, the source object must be a table, a query, or a macro. You cannot export forms, reports, and modules from an MDE or ACCDE file.

 Note   If the database is read-only, or you don't have permissions to make changes to the database, you can complete the export operation, but cannot save the export specification.

  1. Identify the object that you want to export. If this is the first time you are exporting data to an Access database, remember the following:
Element Description
Single object per operation You can export only one object at a time. To export multiple objects, repeat the export operation for each object, or do an import operation from within the destination database.
New table

Each export operation creates a new object in the destination database. If an object with the same name already exists, you can choose to overwrite the existing object or specify a different name for the new object.

 Note   An export operation cannot add records to an existing table. To add records, consider creating an append query. For more information, see the article Create an append query.

Exporting a linked table

If the table that you want to export is a linked table, the export operation creates a linked table in the destination database. The new linked table links to the original source table.

For example, if you export a linked table named Employees1 in the Sales database that is linked to the Employees table in the Payroll database, the export operation creates a linked table in the destination database. The new linked table links directly to the Employees table in the Payroll database.

Partial export You cannot export a portion of an object or just a few selected records.
Relationships Since you can only export one table at a time, the export operation does not copy relationships. If you want to import multiple tables and their relationship, open the destination database and import the objects.
Table definition You can choose to export an entire table or just the table definition. Exporting the definition creates a blank copy of the table in the destination database.
Record source Exporting a query, form, or report does not automatically export the underlying record sources. You must export the underlying record sources — otherwise, 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 export the related table or query if you want the destination field to display lookup values. If you do not export the related table or query, the destination field displays only the lookup IDs.
Subforms and subreports When you export a form or report, subforms and subreports included in the form or report are not automatically exported. You need to export each subform or subreport and its underlying record source separately.
  1. Open the destination database. The file format can be either MDB or ACCDB. It can also be an MDE or ACCDE file. Ensure that it is not read-only and that you have permissions to modify it.

 Notes 

  • If you cannot open the database, another user may have the database open in exclusive mode. You typically need to find that user and request that they close and reopen the database in normal (multi-user) mode.
  • If the database is password-protected, you will be asked to enter the password each time you run the Export Wizard or the saved specification.
  • If you want to export the object to a new database, you must create a blank database (that does not contain any tables, forms, or reports) before starting the export operation. Click the Microsoft Office Button Button image, and then click New.
  1. If an object with the same name as the source object already exists in the destination database, you must decide whether you want to overwrite the existing version or specify a different name for the new object.

You are now ready to start the export operation. Go to the next set of steps.

 Note   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 export operation. For more information about creating an append query, see the article Create an append query.

Run the export wizard

  1. On the External Data tab, in the Export group, click More, and then click Access Database. Button image

 Note   The Export commands are available only when a database is open.

  1. In the File name box, specify the name of the destination database and then click OK.
  2. In the Export dialog box, change the name of the new object if you do not want to overwrite an existing object with the same name in the destination database.
  3. If the selected object is a table, specify whether you want to export the table's definition and data, or only the definition.
  4. Click OK to finish the operation.

If an object with the same name exists in the destination database, you will be prompted to either overwrite or specify a different name. Click Yes to overwrite, or click No to return to the Export dialog box. Specify a name that is not in use in the destination database, and then click OK.

Access exports the objects. If an error occurred, Access displays an error message. Otherwise, the final screen of the wizard appears, and you use that screen to create a specification that saves the details of the export operation.

Save the export specification

  1. Click Save export steps to save the details of the operation for future use. You save the details when you need to repeat the operation at a later time without having to walk through the steps in the wizard each time.
  2. In the Save as box, type a name for the export specification. Optionally, type a description in the Description box.
  3. If you want to perform the operation at fixed intervals, such as weekly or monthly, select the Create Outlook Task check box. This will create an Outlook task that will let you run the specification with a click of a button.
  4. Click Save Export.
  5. Do one of the following:
    • If you selected the Create Outlook Task check box, Access will start Microsoft Office Outlook 2007. Follow the remaining steps in this procedure to configure the Outlook task.
    • If you did not select the Create Outlook Task check box, the operation is now complete. Open the destination database and review the exported object to ensure that the operation was successful.
  6. In the Task window in Outlook, review and modify the task settings, such as Due date and Reminder.

To make the task recur, click Recurrence. For more information about task settings, see Outlook Help.

  1. Click Save and Close.
  2. In Access, open the destination database and review the exported object to ensure that the operation was successful.

Run the export specification

Use the following steps to run the export specification from the Outlook task. Access does not need to be running.

  1. Do one of the following, depending on when you want to run the export:
    • At the scheduled time    When Outlook displays the Reminder dialog box for the task, click Open Item.
    • At any time    Open the task in Outlook by clicking Tasks in the Outlook Navigation Pane, and then clicking the task that you want to run.
  2. On the Task tab, in the Microsoft Office Access group, click Run Export Button image.
  3. Click OK to perform the operation.

Top of Page Top of Page

 
 
Applies to:
Access 2007