Recordset not updateable w/ two one to one tables

[ittechguy]

I have a very simple query here which queries a main table that stores equipment information, and two other tables which store IP addresses (which relate to equipment). Because one equipment can only ever have one IP address and IP addresses are always unique, I've set the FK field in the equipment table to be indexed and not allow duplicates, and I've done the same for the primary key (autonumber) field in my two IP address tables. The indexes seem to make it form a one to one relationship automatically.



I've noticed that the recordset is not update-able if I build a query containing these three tables. I can force it to allow new records if I select dynaset, inconsistent updates. But I'm not gonna do that. Another way I can get it to make the recordset update-able is if I remove one of the two IP address tables, but that is not an option. I can also change the index on one of the ip address table's FK to allow duplicates. If I do that, then updates are allowed. But thats not an option I like.

Here is the SQL of my query. Its actually part of a much larger query, but I'm unable to get this working so there's no point in adding all the rest.

SELECT tblTaclanes.tblPTAddressesFK, tblPTAddresses.PTAddress, tblCTAddresses.CTAddressFROM tblPTAddresses INNER JOIN (tblCTAddresses INNER JOIN tblTaclanes ON tblCTAddresses.tblCTAddressesPK = tblTaclanes.tblCTAddressesFK) ON tblPTAddresses.tblPTAddressesPK = tblTaclanes.tblPTAddressesFK;

[Micron]

Sounds to me that in order to get what you want for what you describe, the PKs in both of the address tables would have to be the right PK so as to match up to ONE FK in the equipment table. I don't see that happening - at least not reliably. You might have to post more info about the structure of all 3 tables and data samples in order to make much sense of the sql statement. It's too hard, if not impossible, to correlate the tables and field links with the info provided. A zipped copy of the db may prove most useful, but I'm limited to version 2007 so can't help if I can't open it. That would depend on whether or not you used incompatible features.

[ittechguy]

Thanks for your fast reply!

Are you saying that the PKs from both IP address tables would have to match to only one FK in the equipment table? I've attached a photo. As you can see, I have two one to one relationships. I require two different IP address tables because I have two different types of IP addresses and they need to be separated.

I wonder if I'm breaking some rules with how I've setup the relationship (and the indexes) on these two tables. I've given much thought into planning this database, and this seems to be the best way to do it. But I'm running into problems, so obviously I'm doing something wrong. I note I have not seen (or used) one to one relationships much, and can't seem to find much info online on them.

[ittechguy]

I've been fooling around with a few test tables and a test query. I was able to get this to allow the recordset to be updateable!



Hopefully you can understand my quick work and poor naming. I think if I relate this to my actual db project, I need to link the PK from the equipment table to the a FK in the IP address tables. I'll have to add a FK for tblTaclanes and then I have to re-work my query and my form to allow it to work, but I think I have found a solution.

[Micron]

there's no point in adding all the rest.

All too often, that's an issue in trying to help solve a problem - not enough information or (not in your case) meaningless substitution and simplification of the issue. If I had seen that picture, I wouldn't have interpreted that the left side had two joins from one field to two other fields in separate tables. It's probably there in the sql statement, but you have that knowledge in your head when you post and we don't so it's best to supply it in a way that doesn't require us to build tables and relationships in our heads based on that. I admit, I'm not as good at that as others. Also, sticking with the quote above, you've posted a SELECT statement and ask why you're getting a message about the record not being updatable. Seems to me that is a very important part of the information that's missing. Then again, I never liked jigsaw puzzles too much!
edit: reviewed again and don't even see which part(s) you are trying to update. How you have set cascade updates/deletes may have something to do with it as well. Maybe post the entire update sql or upload a zipped copy of your db.

[orange]

?? Can you spell it out in plain English.

main table that stores equipment information

I require two different IP address tables because I have two different types of IP addresses and they need to be separated.

You have several pieces of Equipment. Each piece of Equipment has 2 IP Address types.

What is the issue? Please describe in business terms (in plain English).