Running Access append query thru VB?

[Sypher2]

Not sure if this is the best way to do this, so maybe somebody has other suggestions...

I'm trying to run an Append query in an Access 97 database. If I run the query through Access itself, it tells me how many records will not be copied due to primary key duplications, etc., but it will ask if I want to continue with the action query. If I say "yes" then it copies all the unique records.

If I run the query thru VB, it gives a run-time error with close to the same text (primary key violations, etc.) The program then crashes. No records are copied.

Is there a way to force the append query to continue? If not, should I use another means of copying unique records to a different table? (i.e. ADO recordsets).

Thanks for any help.

 

[Phi1Smith]

Hi Sypher2

If you use the Access query wizard to create the SELECT part of a 'Find Unmatched Query' and it will only select the records that haven't been appended already, you can then change the query type to append. Change to SQL view and your query should be written for you.

Phil

 

[Norris68]

INSERT INTO Table1 (SELECT * FROM Table2 WHERE Table2.ID NOT IN (SELECT DISTINCT ID FROM Table1))

... should be close. It selects records from Table2 that are not in Table1 and appends then to Table1. This assumes all field definitions are the same for both tables and the PK is called 'ID'.