Rocky Smolin
rockysmolin at bchacc.com
Wed Jan 11 10:25:27 CST 2012
The PKs of the old table and new table match up. I'm going to send this back end to the client and have them check it out. But it looks good at this point. R -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Wednesday, January 11, 2012 8:21 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Sleuthing - query has become non-updateable Hi Rocky, Missed a bit from my last post. "That will assure you that the related records in LotDetail"... are still linked to the correct parent records. If there is no other unique field in the table you may have to try building one based on the concatenation of the PK field (which we know is unique) and some other field in the tables. The resulting calculated field will be unique too. So do that in two queries, on for each copy of the table, and then use those tables as the source for the unmatched query. Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Wednesday, January 11, 2012 11:14 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Sleuthing - query has become non-updateable Not even Redmond knows! :-) You might want to confirm that the PK values in the new table all match the PK in the corrupted table. If there is any other (reasonably) unique field in the table then you could do an unmatched query joining on both the PK and the other field to see that everything matches. That will assure you that the related records in LotDetail. Like this... SELECT LotControl.PK, LotControl.Other_Unique_Field FROM LotControl LEFT JOIN LotControl_Original ON (LotControl.Other_Unique_Field = LotControl_Original.Other_Unique_Field) AND (LotControl.PK = LotControl_Original.PK) WHERE (((LotControl_Original.PK) Is Null)); Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Wednesday, January 11, 2012 10:55 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Sleuthing - query has become non-updateable Lambert: Did work! Or appears to anyway. But why? Or do I need to know? :) Thanks Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Wednesday, January 11, 2012 7:44 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Sleuthing - query has become non-updateable Sounds like the table LotControl has become corrupted. Have you tried running a make table query to create a copy of LotControl? After that make sure the copy still has a PK setting for the LotID field in the copy, then rename the two tables so the new one winds up being called "LotControl". Might work. Lambert -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Wednesday, January 11, 2012 9:55 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Sleuthing - query has become non-updateable More info: In the References diagram there is no join between the PK LotID of the LotControl table and the FK LotID of the LotDetail table. When I try to create one with referential integrity enforced I get a message No unique index found for the referenced field of the primary table. However, LotID in the primary table - LotControl - is a PK and autonumber. A quick summation query with count of LotID shows each LotID only occurs once. Any clues there? TIA Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Wednesday, January 11, 2012 6:35 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Sleuthing - query has become non-updateable Dear List: Yesterday a client sent me a back end that had become corrupted - front end would not start because the record source for the opening form referenced a corrupted table. In one of the tables I found a record with #Deleted in all of the fields. The autonumber ID seemed to indicate that there was another record missing - the record before the #Deleted record was 2 less than the record after. But I deleted that record and all seemed well. However, a query for another another sub-form now does not allow a record to be added or edited - there's no empty line at the end of the query and the >* is grayed out. And an attempt to change any record gives me that annoying ding. I ran this query in an older copy of the back end and it works. So I know there's some data problem in the current back end that I'm trying to repair. There are three tables in the query and each in table view allows adding and editing of records. But if I structure a query of just two of them - a LotControl table (LotID is PK) and a LotControlDetail table (LotID is FK) the query is not updateable with any of the three join types. I haven't a clue as to where to look next but I suspect, of course, that the LotID is hosed in one or the other table. But inspecting the LotID visually shows nothing out of the ordinary. Any ideas on where to look next to identify the offending record(s)? MTIA Rocky Smolin Beach Access Software 858-259-4334 www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com <http://www.e-z-mrp.com/> Skype: rocky.smolin -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com