Key Violation Problem

[StephenS]

I am a bit new to using transactions. I suspect this stuff is basic to some of you. I am designing an Access application that has an application side (*.mdb file with forms, code etc.) and a data side (*.mdb file with linked tables). The data side has a main table and three other data tables. Relationships between tables are set on single key fields and the tables are linked to the application side.

Whenever I try to append a record to the tables, I get a "Key violation Error". How do I append? I have been trying to do use a transaction so that I can set up the values correctly and append them all at once. Cascading updates do not appear to be supported for linked tables. Are transactions supported? Does anyone have any code or suggestions?


StephenS

 

[Pat Hartman]

Based on your question, the cascade update option does not work as you think it does. When checked, this option will cascade any key value changes to related tables. For example - you have tblA and tblB. tblA has a primary key of keyA. tblB has a primary key of keyB and a foreign key that references keyA in tblA. If you change the value of keyA in tblA, Access will change the value of keyA in all the cooresponding rows of tblB. It will not insert rows into tblB.

An append query can only append rows to a single table. If you need to append rows to several tables, you need a separate query for each. A transaction can be "wrapped" around this process and that will allow you to rollback all changes if the update of any single table fails. If all updates are successful, you can commit the update.

The "key violation error" could be caused by a duplicate key. Make sure you don't have any extraneous unique indexes on the tables. Access has a "feature" that I'm sure was introduced by a programmer in a drug-induced stupor. In Tools/Options - Tables/Queries tab - make sure the "autoIndex on Import/Create" field is empty. If it contains values, Access may be creating extra indexes that you don't need or want. Go through each table in design mode and delete any of these automatically genereated indexes.

 

[StephenS]

Pat, thanks for your reply. It got me on the right track. Foreign keys in other tables were causing most of the problem. I now have two of the four tables allowing me to append to them. Part of it also seems to involve the order in which a record is appended. If I append a record to the "one" side first, it works better than if I try to append to the many side first. Two tables are still not working. I haven't found a key problem with them. The main difference is that one of them is a junction table between the main table and the other. The relationship looks like this:

Table 1---oo juncTable 1---1 MainTable

So far, all of my changes to the order here have still given me key violation errors. Changing the structure might work, but my normalization might suffer. Any other ideas are appreciated. But, I am making progress... Thanks.

StephenS

 

[Pat Hartman]

When you are inserting rows in a junction table, the rows from the two tables being related must already exist. For example, if you are creating a table that links students with classes. The student must already exist in the student table and the class must already exist in the class table. Only than can you insert a row in the junction table that links a student to a class.

 

[StephenS]

What you said about the order of inserting records in a junction table is correct - but there's one more detail that can mess it up. If I check Cascading updates and deletes for the Relationship between the Main table and the junction table, it works OK. But if I check it for the Relationship between Table1 and the junction table (in my example), Access will still give a "Key Violation Error" even when adding to Table1 first. I've tried it both ways with the only difference being that I checked the box the first time and unchecked the box the second time. Not sure why that is but I can live with it. I can now Add, delete, or update all four data tables.

StephenS