You can import Access database objects like
tables, queries, forms,
and reports from another Access database. Even if the source for
the form or report will be
different in the new database, the design elements will still be there,
saving you a lot of time and effort.
Access can import tables and other
objects from another Access database.
Where you are:
Project 1: Intro
Project 2: Access Basics
Project 3: Tables & Queries
Project 4: Forms & Reports
Special Forms & Reports
Methods of Importing from Access Database
You import from inside the database
where you want the new data/object.
- Copy and Paste Objects:
Copy an object from the Database Window in one database and paste into
- Copy and Paste Data:
Copy cells from one Access datasheet and paste into another. If you are appending (adding new records) to an
existing table, the data must match the existing field order and the data
types. If you are creating a new table, you can name and configure the
fields after pasting.
| | - The Import dialog
appears, which looks like an Open dialog.
Choose Microsoft Office Access (*.mdb, *.adp; *.mda;*.mde;*.ade)
Select a file.
The dialog Import Objects
in which you can choose multiple objects to import. You can select items
in each of the tabbed pages before clicking on OK.
Import Errors: If some or all of the records fail to import
properly, they will be saved in a new table called Paste Errors. You should
look at the records to see which ones caused a problem. You may be able to
tell what the problem was just by inspecting the records.
Common Causes of Paste Errors:
- Different table structure: Missing or extra fields
Example: Source might have a person's name in 1 field while the
destination table use 3 fields
- Mis-matched data types:
Example: Source may have zip codes in a Number field while the
destination table uses a text field.
- Different field names:
Example: Source may use FirstName,
without a space, while destination uses First Name,
with a space.
- Field order: If the first row of imported data does not contain
the field names, then the fields must be in the same order
as in the Access table.
- Duplicates in primary key: Imported data may have duplicate
values in the field that is the destination table's primary key.
Step-by-Step: Import Access Objects
What you will learn:
|to import objects from another Access database|
Import Objects: Tables and Queries
To prepare for the rest of the lessons on forms and reports, you need
some more tables and a couple of standard queries. For practice in
importing and to avoid a lot more table creation, you can import what you
need from an Access database that was prepared just for this purpose.
- If necessary, open worldtravel.mdb
from your Class disk. This is the database that you created for this
project in an earlier lesson.
- From the menu select | | .
The Import dialog appears.
- Navigate to the folder where you saved your
and select the file worldtravel-forimport.mdb.
The Import Objects dialog appears.
You do not see the file worldtravel-forimport.mdb
Either you did not change the file type to
"Microsoft Office Access (*.mdb, *.adp; *.mda;*.mde;*.ade) "
or you are not looking in the folder where you put the resource files. If necessary,
resource files again.
the Tables tab, click the button Select All. All of the
objects on this tab are selected for import.
- Click on the Options tab and inspect the
choices. The default choices are good for this lesson - Relationships,
Definitions and Data, As Queries.
on the Queries tab.
- Click the button Select All.
- Click on OK.
The selected objects are imported into worldtravel.mdb.
- In the Database Windows, inspect the list of Tables and the
list of Queries to verify that you imported all the objects that you
intended to import.
If you slip up and import the same object twice, Access does not
overwrite the existing object. It creates a new object with the same
name but with a number at the end, which will increase for each time that
you import the object.
Solution: Delete the duplicates from the Database Window. You will
see one or two messages before the object will be deleted.
confirmation message appears. You have to click on Yes to continue with
relationships were imported with a table, another message appears. You
must delete the relationship before you can delete the table. But Access
kindly offers to do that for you in the warning message box that pops up
when a relationship exists. Clicking Yes deletes the relationship and then