Help Please!! - Append query throw Key Violation Error

Question

Help Please!! - Append query throw Key Violation Error

Darren 0

I have an append query appending records to the table (tblIntake) and I am getting a key violation error and I am trying to figure out why is is throwing a key violation error. I do not write any values to the primary key (IntakeID).

I have done the following so far:

Checked for duplicate primary key values - none in the table tblIntake
Checked for duplicate values in any unique indexes (only the one unique index IntakeID)
Checked to confirm I am not trying to write any value to the primary key (IntakeID)
Checked for any duplicate records in the append query itself. The query is only append one record at a time.
Check for any existing relationships in the database. None show when I use the "Relationships" option on the Database Tools ribbon.

The tblIntake has the following indexes:

Field Name	Primary Key	Unique	Ignore Nulls
BlockID	No	No	No
GrowerID	No	No	No
InTransFruitLog	No	No	No
InFruitTypeIntakeID	No	No	No
IntakeID	Yes (auto number - increment)	Yes	No
GrowerID	No	No	No
VarietyID	No	No	No

In an effort to find which of the fields might be causing the key violation my next step was to run an append query with only one field at a time until I found the field causing the key violation. I have tried two fields SessionID and ChangeDate and both have thrown the key violation error. The SessionID is a unique identifier and now two records would have a duplicate value. The ChangeDate would be the date the record was changed. The query is reading the values from a form. If I change the query to a select query it will display a value for each field.

Can anyone please let me know what might be causing the problem and how I can determine which field is causing the problem?

All help is greatly appreciated.

0 comments

1 answer

Your answer

Answer 1

Dear Darren,

You've already worked through this carefully, which helps narrow it down a lot. From my research, since appending only SessionID fails, and appending only ChangeDate fails too, the problem is probably not either of those fields. It's more likely related to the fields you aren't filling in, or to the table itself. The most likely causes are:

Relationships in the back end database: If tblIntake is a linked table, the Relationships window in your front end won't show relationships defined in the back end. If a field such as GrowerID or BlockID is linked to a parent table with referential integrity enforced, any field you leave blank, or that defaults to 0, can trigger a key violation when no matching parent record exists. You can check this by opening the back end directly and going to Database Tools > Relationships > All Relationships.
A combined unique index: Your index list shows GrowerID twice, which may mean some fields belong to a multi field index. If that index is set to unique, the combination of values can clash with an existing record even when each field looks fine on its own. You can check this in Design View > Indexes.
The AutoNumber seed: Access can sometimes try to reuse an IntakeID that already exists, for example after an import. Back up the database first, then run Database Tools > Compact and Repair Database and try again.

A quick way to tell these apart is to open tblIntake and add a record by hand without entering IntakeID. If that also fails, the cause is in the table. If it works, the cause is in what the append query sends.

I hope this helps you track it down. Looking forward to your reply.

If the answer is helpful, please click "Yes" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in the forum documentation to enable e-mail notifications if you want to receive the related email notification for this thread.