Add tables for the source and destination tables

In Setting up an Import Database we argue the best way to do imports is using a separate Import database. To access the source and destination tables, you will use either imported tables using MS Access built-in Import wizard, or linked tables. When you start Import and Validate / Excel Import and there are not yet any tables in your database, the Create Linked Tables dialog opens, prompting you to add them.

Important: you may also have to link (import) tables with referential integrity constraints from the destination database. These will be use in Lookup, see Extend Field Behavior

Tables: Imported or Linked

For destination tables the logical choice is to use linked tables. That way, after the import the data are in the correct place (production data), and you don't have to worry about copying the imported data tables, which may be involved considering relations between tables which may even cause referential integrity problems.

For  source tables, you can choose either linked tables or imported tables, or a combination. Just decide whichever is best pragmatically.

In the following, we assume you want to import or link to Access tables. The same process however also allows you to import other data sources, such as Excel, SQL server, SharePoint, etcetera.

How to add a linked table or import a table

Access 2007

1. Select Tab 'External Data'
2. Click 'Access' in group 'Import' - the 'Get External Data' appears
3. Browse to the data source to link a table from
4. Select the radio button 'Link to the data source by creating a linked table' (- or Import Tables Queries,... - obviously you will only be importing tables here)
5. (In case if Import, you will have to select which table)
6. Press OK - the 'Link Tables' (- or Import Wizard) dialog appears.
7. (In case of 'Link Tables') Select all tables in the data source you want to link to
8. Press OK - the linked tables have now been added to your database

Distinguishing between Source and Destination linked tables

Quite often the names of the source and destination tables will be the same. If you first add the Destination tables, they will get a normal name, e.g. Customers. If you after that link to the Source tables, those will by default get an '1' appended in case of conflict, e.g. Customers1. It pays to try to stay consistent with names distinguishing between Source and Destination linked tables to prevent confusing which is source and which is destination. As a convention you can rename Source linked tables to always have the same suffix (e.g. 1) or a suitable prefix (e.g. 's'), whichever you prefer.

Next: Setting up an Import