Updating/importing? Question

[Hartkoorn]

Hello all, I have a question;
I have 2 databases with 4 tables in it following the same structure.
Table 1: tblPersons consisting employeenumber as pk
Table 2: tblLocation consisting the location as pk
Table 3: tblCompanyvehicle consisting the licenseplate as pk
Table 4: tblAssets consisting serialnumber as pk
All tables have relationships build on the employeenumber which is a pulldown in the three last tables.
I have a thirdth empty exact same database and want to import the tables from the 2 other databases by clicking a button, browse to the database and click import.
The import should IGNORE duplicates without a message (if New York already excists there is no reason to insert it again, the same goes for employeenumbers, since they are unique, etc, etc).
Is there any way to do this?
Thanks in advance

(I did a search on this, but nothing came up that described the problem)

 

[uplate]

When I was trying to learn how to do this, I spent a ton of time searching. I don't know why this topic seems hard to find answers for.

At any rate, I've found that the following approach works well (at least for smaller databases) if you just want to append new values:

1. Copy the table from the selected source database into the destination database using Docmd.TransferDatabase. Since your tables have the same names, Access will add a '1' on the end of the name of the copy.

2. Write a query that performs an outer join on the original table and the copy (selecting all fields from the copy table), with the criteria that the PK in the original is NULL. This is how to find all the new data. Try something like:

SELECT tblPersons1.employeenumber, tblPersons1.name, etc
FROM tblPersons1 LEFT JOIN tblPersons ON tblPersons1.employeenumber = tblPersons.employeenumber
WHERE tblPersons.employeenumber IS NULL;

(Either create this in SQL view, or copy the table in first to use QBE.)

3. Write an append query that inserts into tblPersons with the data from the first query.

4. Delete the copy table.

(You can do the same thing to update existing records with identical primary keys, if the other fields might be updated, by removing the WHERE clause.)

Put those steps into your code, and you should have no trouble importing data.