How do I resolve Access 2003 Key Violations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

An append query that works in Access 97 does not work in Access 2003.
Error is "Microsoft Access didn't add 3 records to the table due to key
violations"
 
Have you determined which three records didn't get added? You should then be
able to look at the fields in the record and the table design to determine
why the records weren't added.
 
Thanks Duane.
We have looked at the Properties of the records from the query and the
properties of the table being appended to. But cannot resolve the error.
The 3 records referred to in the Error are in fact ALL the records that are
to be appended from the query.
Can I send you a copy of my test database and ask you to investigate?
Thanks
Alan.
 
THIS PROBLEM HAS NOW BEEN SOLVED.
The Error "Microsoft Access didn't add 3 records to the table due to key
violations" occurred because the DATE formats on the table and on the append query were inconsistent.
Click to expand...
SOLUTION: Add a format command to the Month coloumn of the Query, to make
the Date format in the Query consistent with the Date format in the table. As
follows:
Month: Format([Forms]![frm_front]![txt_validate_broker_invoice],"dd/mm/yyyy")
 
No, I prefer to handle questions in the news groups.
There are a number of reasons that records won't be appended:
- no related record in another table
- duplicate key values
- missing/null values
- values outside of ranges
- others
You just need to spend the time with sample data and making small changes.
 
It's clear that the user can set up custom queries to determine which records failed key violation during an append, but does anyone know if MS Access can be made to automatically determine and store these records in a temporary table (in a similar fashion to the Paste Errors table) ?

As it stands, the MS access dialogue box prompt is initially useful to the user, but ultimately not that helpful in searching for what can often be a needle in a haystack.
 
Back
Top