Why am I getting a key violation when appending records?

[Russ Slater]

Hi,
I am currently getting a very odd key violation when appending values to a table. The strangest thing is that it is only happening some of the time.

I have a form where people can create schedules. They can create single day events or they can select events that happen every Tuesday from today until the last day of September for example. Their schedule gets parked into a temporary table where they can then edit any details (for deleting bank holiday dates, etc.,) and they then click a button to append the data to the schedule table.

When I do single-day events they append fine, but with the multiple dates I am having some problems and can't work out why. This is because there seems to be no rhyme and reason. When I select dates that are on the same dates as ones already in the schedule table I sometimes get a 'key violation' error. This only happens sometimes. For instance I just chose 10 different dates to append, all which were already featured in the schedules table and only one had a key violation. I then tried adding the same dates again with different data and all 10 had key violations.

In the schedules table the primary key is an autonumber. I have set the 'dates' field as Indexed Yes (Duplicates Ok) and made sure that all other fields are either not indexed or specify Duplicates Ok.

Can anyone think what this problem could be? I am really at a loss at the moment. I keep testing to try and find the problem but just can't work out why exactly this error is coming up.

Thanks!
Russ

[Russ Slater]

I can now definitely say that it is a problem with the 'dates' field. I have changed the append query so that the only fields being appended are the 'date' field and one other and each time there is a key violation. If I append all data except for the 'date' field it appends file and if I only append the 'date' field it works fine, but as soon as I try and append the 'date' field along with any other field, be it a number, text or memo it has a problem.

What the devil is going on? Has anyone experienced anything like this before?

[NeoPa]

Russ Slater:
In the schedules table the primary key is an autonumber. I have set the 'dates' field as Indexed Yes (Duplicates Ok) and made sure that all other fields are either not indexed or specify Duplicates Ok.

I must admit, I was confident it was an index or relationship issue until this bit.

I'm sorry to doubt you, but could you double check the indexes, and perhaps also look at the Relationships window to see if anything is set up there that may be relevant.

Welcome to Bytes!

[Russ Slater]

I've double checked the Relationships. There is nothing there. I've never even got to the point of creating any relationships yet so that's not too surprising really.

Checked all the indexes too. They are all 'no' or 'Yes (Duplicates OK)'. What's the recommended status for these. Is it best to index them or better that they are all set as 'No'?

[Russ Slater]

It now seems to be working fine. I deleted all the records in all the tables I had been working with, and hey presto, it was working after that.

I'm not sure I'll ever understand Microsoft Access!

[NeoPa]

Well Russ, indexing is about various issues, but foremost among these is the ability to find or link records by a particular field or set of fields. The uniqueness is dependent simply on whether or not the field could validly hold the same value more than once. A PK should always be unique as it is the principle identifier for the record. An example would be a Product Code field for a Product table. Only one Product in the table could ever have any specific value. Two products with the same Product Code wouldn't make sense.

A Product Group field, on the other hand, may require a non-unique index. Reporting on products by Product Group would be greatly helped by such index, but making it unique would mean only one product could belong to any group. Not a good plan.

I'm glad your problem got resolved anyway. I've little idea what may have caused it but it may have been related to corrupt or invalid data already in the table.