Violence due to primary key problem in Append Query

[Guest]

I created an append query to append the data to Update table. However, in the
Update table I put the primary key on purpose in order to prevent duplicated
record. Upon running, it shows the error message "Microsoft Access cannot
append all the data in the Append query".

Is there anyway to turn this message off and append the data automatically
without show this messge panel?

Thank you

 

[Neil Sunderland]

I created an append query to append the data to Update table. However, in the
Update table I put the primary key on purpose in order to prevent duplicated
record. Upon running, it shows the error message "Microsoft Access cannot
append all the data in the Append query".

Is there anyway to turn this message off and append the data automatically
without show this messge panel?

It would be easier to alter the query so it doesn't try to append
records that already exist.

You'd need to add a WHERE clause, something like:

WHERE NOT EXISTS
(SELECT * FROM CopyToTable
WHERE CopyToTable.KeyField = CopyFromTable.KeyField)

 

[Steve Schapel]

Vicky,

It depends on how you are going about running the query.

If you are doing it via a macro using the OpenQuery action, you can
precede it by a SetWarnings/No action.

If you are doing it via a VBA procedure, using the DoCmd.OpenQuery
method or the DoCmd.RunSQL method, you have a couple of choices. One is
to put
DoCmd.SetWarnings False
.... before the query in your code, and then
DoCmd.SetWarnings True
afterwards.
Another approach would be to use the CurrentDb.Execute method instead.