Why would my Access 2007 query suddenly become not updateable?

[ojeffery]

I have a query in Access 2007. It's worked fine for months, but I'm suddenly getting a "the recordset is not updateable" error. Thinking an error must have been caused by a recent change, I went back to archived versions (that definitley worked) - they're all chucking out the same error. The table itself is updatable; indeed, another query on the same table works just fine. What could have suddenly happened to break my query? Code follows:

 SELECT Prospects.Company, Contactnames.*, IIf([Prospects]![Key Contact]=[ContactID],True,False) AS [Key Contact], Prospects.Status
    FROM Contactnames INNER JOIN Prospects ON Contactnames.CompanyID=Prospects.ID
    WHERE (((Prospects.Status) Not Like "Duplicate"));

Any help would be greatly appreciated. Thanks, Oli.

 

[Pat Hartman]

What is the PK of contactnames?
What is the PK of prospects?

ID is a poor choice for the name of a PK. I know that's what Microsoft uses in examples but they don't do real development with Access and so they don't follow good naming standards so I rarely recommend their examples.

CompanyID does not seem to be the correct field to be joining on. I think you are ending up with a cartesian product (which is not updateable) because the data is no longer 1-1 or 1-m but is now m-m because you are joining on the wrong field.