Forms & Reports:
Import Access Objects

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


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.

Diagram: Exported from Access to Access

Access can import tables and other database
objects from another Access database.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/Link Arrow: subtopic open
    Icon: StepImport Spreadsheet
    Icon: StepImport Text File
    Icon: StepImport Access Objects
    Icon: StepLink
    Icon: StepExport from Access
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics


Search  
Glossary
  
Appendix



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 another.
  • 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.
     
  • File types for importing from Access databaseMenu:
      File | Get External Data | Import  - The Import dialog appears, which looks like an Open dialog.
    Choose Microsoft Office Access (*.mdb, *.adp; *.mda;*.mde;*.ade)
     
    Select a file.
    Dialog: Import ObjectsThe 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:

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

Icon: Step-by-Step 

Step-by-Step: Import Access Objects

 Icon: Step-by-Step

What you will learn:

to import objects from another Access database

Start with:  Class disk,worldtravel.mdbresource files

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.

  1. If necessary, open worldtravel.mdb from your Class disk. This is the database that you created for this project in an earlier lesson.
     
  2. From the menu select   File | Get External Data | Import .
    The Import dialog appears.
     
  3. Navigate to the folder where you saved your resource files and select the file worldtravel-forimport.mdb.
    The Import Objects dialog appears.
    Icon: TroubleProblem: 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, download the resource files again.
     
  4. Dialog: Import Objects - Tables, all selectedOn the Tables tab, click the button Select All. All of the objects on this tab are selected for import.
     
  5. Click on the Options tab and inspect the choices. The default choices are good for this lesson - Relationships, Definitions and Data, As Queries.
     
  6. Dialog: Import Objects - Queries, all selectedClick on the Queries tab.
     
  7. Click the button Select All.
     
  8. Click on OK.
    The selected objects are imported into worldtravel.mdb.
     
  9. 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.
     

Database Window: Duplicate tablesIcon: TroubleProblem: Imported Twice
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.
Message: Do you want to delete the table 'Trips1'?A confirmation message appears. You have to click on Yes to continue with the deletion.
 

Message: Can't delete table until its relationships to other tables have been deletedIf 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.