Doug Steele
dbdoug at gmail.com
Wed Jan 11 10:18:25 CST 2012
I can't tell you why, but every once in a while I have the problem where a corrupted row in a table changes the table definition, removing the primary key designation of an autonumber field. At the same time the autonumber value sequence has gone bad, and the database tries to create duplicate autonumber values. I have had to rebuild the table, as Lambert suggests. Doug On Wed, Jan 11, 2012 at 7:54 AM, Rocky Smolin <rockysmolin at bchacc.com>wrote: > 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 >