Append New from External Database

[tcjones]

Hi there I'm attempting to import records from an external database without losing the records that I currently have.

Eg if I have 3 records in my main database and 5 in the external one, I only want to append the 2 missing records and leave the other 3 alone.

Ideally what I want to do is the below

SELECT * INTO table
FROM [MS Acess;DATABASE=\\path\external_data.mdb].

WHERE id NOT IN (SELECT id FROM table)

Any ideas where I'm going wrong with the above?

Thanks for your help!!!

Tony

 

[neileg]

If you make ID an indexed field, no duplicates, this will prevent you appending records with IDs that already exist.

 

[tcjones]

Thanks Neil, the issue I'm facing is that when I run the query it wipes all of the records that currently exist in the main table.

Eg if i have 10 records in my main table and run the query on the external table that has 2 records my main table will be left with these 2 records... and the other 8 will be deleted.

Is there something wrong with my SQL syntax or can't this be done?

Tony

 

[neileg]

The SQL doesn't look right to me. An append query should look like this:
INSERT INTO tblOne
SELECT tblTwo.*
FROM tblTwo;

 

[tcjones]

You make a good point, worked great thankyou kindly!