One source table, multiple destination tables

This tutorial shows how to create an import to add new products and new suppliers to the Products and Suppliers table in one go.

Note: this example uses several techniques and concepts which will usually be familiar to MS Access developers only: relationships, elementary VBA code. So this is not for the faint of heart.

In the Relationships window we see how the Suppliers ( parent) and Products ( child) data are related.

This schema requires for each NewProductsWithSuppliers record to  first add a Supplier if it does not yet have a record in the database, followed by adding the Products records, using the newly created SupplierID.

This example shows the following key features of Import and Validate:

  • manually set Source
  • looking up a value using the evaluate function: given the CompanyName, what is his ID
  • picking up the ID of a newly created parent record
  • using the parent ID to connect child records to the parent
  • keep old values

The import example can be found in the MyImport demo database:  Start > All Programs > Import and Validate > MyImports.mdb

New Import - Name and Purpose

  1. start New Import - Name and Purpose 
  2. Enter the name Add New Products With Supplier
  3. Select in group box 'Purpose of import' > 'Add: new records are added to the destination table'
  4. Press [OK]

In the Import an Validate form the Select Source Table tab gets focus.

Select Source Table

  1. In the combo box 'Select Source Table or Query', select the NewProductsWithSupplier table. The source contains the records to be imported

After selecting the source table, the Select Destination Table tab gets focus.

Select first Destination Table: Suppliers 

We now want to select the first Destination Table, in our case this will be table Suppliers.  The Supplier must be known before Products can be added because of the parent-child relationship (referential integrity).

  1. Press button [Add destination table ...]

The [Select Destination Table] dialog appears

  1. Select the Suppliers table.
  2. Press OK

In the Transfer Pane rows appear for the  Suppliers destination table. You will notice there is initially no match with the Supplier. The reason for this is there is practically no overlap between the Suppliers table and the NewProductsWithSupplier table.

Manually set Source

Only Companyname for the supplier is directly available, and that can't be matched automatically, so we have to match that manually:

  1. In row [CompanyName], in column Source, select from the combo box 'Supplier'

The New Value cell gets filled with the name of the supplier

Keep old value

We have choosen Add & Replace as the way to add records, because this way if the Supplier already exists, this does not result in an error, and we will still have access to the SupplierID, which we will need for adding Products. However we don't want to change the supplier name: 

  1. set the [UseOldValue] on the CompanyName row (on the complete right).

Looking up a value using the evaluate function: given the CompanyName, what is his ID

To find what is the SupplierID of a company, we use a simple Visual Basic function (VBA), applied to the supplier's CompanyName.

  1. In row [SupplierID], in column Source, again select from the combo box 'Supplier'

The New Value field again gets the name of the company. This time however, it turns orange. This indicates the value is not acceptable. The reason is only numeric values are acceptable for the SupplierID field. We will fix this by making a function that looks up corresponding SupplierID.

  1. Switch to tab [Extend Field Behaviour]

  1. Check 'Evaluate VBA expression to obtain New Value'

A column 'Evaluate' appears next to the Source column.

  1. On the SupplierID row, click inside the cell in the Evaluate column

A dialog appears 'Do you want a VBA evaluation function to be created?'

  1. Press [Yes]

The above has two effects:

  1. The selected cell gets filled with a VBA function call 'Supplier_Evaluate([Source])'. The purpose of this call is to pick up whatever [Source] returns as a value (in our case the CompanyName), feed that value to the function Supplier_Evaluate and put the result in the New Value cell.
  2. In the Access VBE (Visual Basic Environment of Access) the function has been added.

It is now your task to change the dummy function that was automatically created into one that looks up the Supplier ID corresponding to the Supplier companyname.

  1. Press Alt-F11 to open the Access VBE.

The green Example code suggests how you could alter the function to make it return what you need. In our case indeed we will be using the MS Access' DLookup function

  1. Change the function as below

Function Supplier_Evaluate( str  As  StringAs  Variant
On  Error  Resume  Next
    Supplier_Evaluate = DLookup("SupplierID", "Suppliers", "CompanyName='" &  str & "'")
End  Function

  1. Make sure your code compiles ok, else you'll get errors while trying to import: menu > Debug > Compile
  1. Save the changes  and close Access VBE

If all's well, you should now see the SupplierID appear instead of the CompanyName in the New Value column.

  1. Finally, we may remove the unused rows: all rows starting from Contact Name. They don't play a part in the current import.

See  Remove unused rows from the Transfer Pane.

Select second Destination Table: Products

We now want to select the second Destination Table, in our case table Products.  

  1. Press button [Add destination table ...]

The [Select Destination Table] dialog appears

  1. Select the Products table.
  2. Press OK

In the transfer pane, an again matching is done of Source and Destination fields. Note that on row 231, the Source column value is 216.Suppliers.SupplierID. This tells the program to pick up the New Value of line 216. In case a new Supplier is added, the newly created SupplierID is automatically picked up and used to link the added new Products.

Import record - existing supplier

  1. Switch to tab Start Import.
  2. Press button [ Process Record

The record was added, the next record was selected.

If you open table Products, you will see 'Vegie-spread' was added. Close the table again.

Import record - new supplier

  1. Go to the last record using the Navigating buttons. The last record happens to be a new supplier (New Kid on the block)
  2. Press button [ Process Record

The records for the new supplier and product were added, the next record was selected.

If you open tables Supplier and Products, you will see 'New Kid on the block' and 'Tofu' were added.