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
database
objects from another Access database.
You import from inside the database where you want the new data/object.
Import Wizard:
Ribbon tab External Data > Button for Access
In the File Open dialog, browse to select a file. Then pick whether you want to import an object or link to a table.
For importing, the dialog Import Objects appears, 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:
![]() |
Step-by-Step: Import Access Objects |
![]() |
What you will learn: | to import tables and queries from another Access database to correct import errors/inconsistencies to create lookup fields from imported objects |
Start with: , worldtravel-Lastname-Firstname.accdb from previous lesson, resource files
You can import what you need from another Access database instead of having to recreate it. Sometimes you want just the structure and properties of a table without the actual data.
In this section you will import some tables and queries. In the next section you will learn how to create your own queries. You will use this database in the next project, Forms & Reports, so don't lose it!
From the ribbon tab External Data in the Import & Link tab group, click on the Access button .
The Get External Data - access Database dialog appears.
Browse to the folder where you saved your resource files and select the file worldtravel-forimport.mdb.
Problem:
You do not see the file worldtravel-forimport.mdb
Either you did not download the resource files or you are not looking in the folder where you put the resource files. If necessary,
download the resource files again or the file worldtravelforimport.mdb.
Click on OK.
The selected objects are imported into worldtravel-Lastname-Firstname.accdb.
The dialog will show what object is currently being imported but you have to have a quick eye!
In the Navigation Pane, inspect the Tables and Queries to verify that you imported all the objects that you
intended to import.
The database worldtravel-Lastname-Firstname.accdb now has 6 tables and 2 queries.
Problem: Imported
Twice
If you slip up and import the same object twice, Access does not
warn you or 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 Navigation Pane. You will
see one or two messages before the object will be deleted.
A
confirmation message appears. You have to click on Yes to continue with
the deletion.
If
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
the table.
When you import data, the new data may not fit quite right with the tables and queries you already had. You may need to create the proper relationships. Access will not import AutoNumber fields from another Access database. Paste errors can create some numbering gaps, like what happened for the ClientID field in the Clients table. So some records may not match up correctly.
The tables you have created do not have many records, so you could look at the records yourself and make correction. But the Query Wizard can help you create a Find Unmatched query that will search ANY number of records in the imported Trips table that do not match up with the existing tables in the database.
Select the table Clients and click on Next >.
Click on Finish.
The query runs and shows the datasheet of results. There are two trips.
Import Error: One unmatched trip has a ClientID of 12 but there is no client with that ID number. Remember the skipped numbers in the AutoNumbers when there were Paste Errors. So this error might be an import error. Or it could be a data entry error where the wrong client was selected.
Input Error: The second trip has a ClientID of 0. That could happen when the Trip record was entered BEFORE the matching Client record was created and the Trip record was never updated. That is not supposed to happen!
Now that you have found unmatched trips the problem to solve is which client does each trip go with.
Similar queries can be created for the AgentID and DiscountID fields. Those do not have unmatched records this time, so you won't have to go through the process again.
Now that you have all of the tables needed, you can make some of the table fields into lookup fields. That lets your users pick from a list and see the list choices instead of the ID codes that are actually saved in the fields.
Delete Relationship First: If a relationship exists to the Divisions table, you would have to delete the relationship first, create the Lookup field, and then recreate the relationship.)
Click on Yes.
Another message tells you that some data may be lost. The field size will be changed for the field.
Open the table Staff in Datasheet View and verify that the two fields now show text instead of ID numbers.
Clearly the width and alignment of the Location column needs some work. In the next project you will create a data entry form for this table. So the layout in Datasheet View does not really matter.