Tables in my db require compound (composite) keys. I'd like to enforce referential integrity but access determines the relationship as indeterminate.

I should also say that the compound key of one table consists of 3 fields and its related table has 2 fields as the compound key (which it shares with the former table)

Please can anyone let me know whether there's a way for access to do cascading updates and deletes in this situation, and/or enforce referential integrity and the correct relationship. It seems logically possible!!!!

thanks

Your primary keys are not properly defined. Once they are fixed, Jet will be able to determine the cardinality of the relationship.

Not sure what you mean by "not properly defined"?
The composite primary key for each table is its unique identifier.

The composite primary key of table A (2 fields) is also the foriegn key that relates it to table B.

Table B's composite primary key (3 fields) consists of the 2 primary key fields that also exist in table A and another field.

Ok..that's succinct but maybe not clear :) sorry about that.

Um...yep...so back to the original question. I'm still not sure what's wrong and what's not properly defined?

Thanks

Notice that the two key fields in the first table and the three key fields in the second table are bold. This means that they are defined as the primary key. You may have only specified one of the keys in each table when creating the relationship. Jet would call the relationship indeterminate in that case.

Hmm...now I am baffled...the diagram you provide describes exactly the setup I have, however access still specifies the relationship as indeterminate :( on my db.

Since time is not on my side I've had to make about 30 changes to my code as a result of this "anomally" rather than bottom out why access doesn't pick up the relationship correctly :(

I hope it's not a bug, because some of the other Access bugs I've come across were a pain, the last thing I need is a really serious one like this.

You should not be making code changes to accomodate this. This is not a bug. There is some problem with your definition. Upload a db with just the problem tables.