[AccessD] Sleuthing - query has become non-updateable

Rocky Smolin rockysmolin at bchacc.com
Wed Jan 11 09:54:59 CST 2012


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



More information about the AccessD mailing list