CODEWITHSUNDEEP
sqlms-accessms-access-2007recordset
Sinister Beard
Sinister Beard

Reputation: 3680

Why would my Access 2007 query suddenly become not updateable?

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.

Upvotes: 2

Views: 11159

Answers (2)

mwolfe02
mwolfe02

Reputation: 24207

If you are using linked ODBC tables, you need to include the primary key field(s) from all tables in the query if you want the query to be updateable. Here are some potential "gotchas":

  • Access may not recognize the primary key fields correctly in a linked ODBC table; often (always?) Access picks the first unique index it finds for a table (based on alphabetical order of index name) and assumes that index is the primary key
  • adding replication to tables in MS SQL Server (and perhaps other RDBMS's) will add a GUID column with a unique index; along with the above point, this can cause Access to think your linked tables have different primary keys than they really do
  • Changes made to the design of ODBC linked tables are not automatically reflected in Access; linked ODBC tables can be refreshed via Tools --> Database Utilities --> Linked Table Manager... (among other ways)

Upvotes: 5

Guffa
Guffa

Reputation: 700282

The likely reason is that it's not the query that has changed, but the database.

Check that the database file hasn't been write protected. That would cause that error message.

Upvotes: 0

Related Questions