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

 

[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

 

[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