Advertisements

Export a database object to another Access database

Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Access supplies various avenues to copy an object such as a table or form from one database to another. Copying and pasting an object is simplest, but exporting an object provides you with further 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 even save the details of the operation as an export specification for future use.

Overview

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

You export database objects at the required time by performing 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.

Consider that exporting an object to a different database hardly varies from launching a second database and then importing the object from the first. The two key variations between importing and exporting objects between Access databases are:

  • You can import several objects in a single operation, but you cannot export numerous objects in a single operation. If you want to export many objects to a different database, it is convenient to enter the destination database and then conduct an import operation from within that database.
  • Further to database objects, you can import relationships between tables, coupled with any import and export specifications, and menu bars and toolbars. You can even import a query as a table. Exporting comes without these options.

Looking for information about importing objects into an Access database? See Import database objects into the current Access database.

Prepare for the export

  1. Access 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 needs to 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:
ElementDescription
Single object per operationYou 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 tableEach 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 Add records to a table by using an append query.
Exporting a linked tableIf 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 exportYou cannot export a portion of an object or just a few selected records.
RelationshipsSince 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 definitionYou 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 sourceExporting 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 fieldsIf 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 subreportsWhen 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. Enter the destination database. The file format can be either MDB or ACCDB. It can also be an MDE or ACCDE file. Check 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.

  1. If an object with the same name as the source object is established in the destination database, you have to choose between if you want to replace the existing version or enumerate another name for the new object. You can now begin the export operation. Visit the next set of steps.

Export a database object to another Access database

  1. On the External Data tab, in the Export group, choose Access

Tip: You can also start the export process by right-clicking the object in the Navigation Pane and then clicking Export > Access.

  1. Access displays the Export – Access Database dialogue box.

  1. In the File name box on the Export – Access Database dialogue box, set a unique name of the destination database and then press OK.
  1. In the Export dialogue box, edit the name of the new object if you do not want to overwrite an existing object with the same name in the destination database.
  1. If the chosen object is a table, specify whether you want to export the table’s definition and data, or only the definition.
  1. Select OK to conclude 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. Press Yes to overwrite, or click No to return to the Export dialogue box. Distinguish this object by giving it a meaningful and unique title for use in the destination database, and then click OK. Access exports the objects. If an error surfaced, Access presents an error message. Otherwise, the final screen of the wizard emerges, and you use that screen if you want to formulate a specification that saves the details of the export operation.

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

Leave a Reply

%d bloggers like this: