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
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.
Access 2007
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
Setting up a new Import Database |
---|