Suddenly non updateable recordset

[Isskint]

Hi all

A database that has been in use for 3 years, has suddenly today decided a query data source is not updateable. The query includes PK and FK from all parent record sources. The parent record sources are updateable manually.

I tried deleting and recreating the query and form that are causing this, but no change.

I have implemented a workaround for now by setting the Recordset Type from 'Dynaset' to 'Dynaset (Inconsistent Updates)' on the offending form.

Does anyone have a clue why this would happen and how to resolve it in a better way than changing the Recordset Type?

 

[isladogs]

Not really possible to give you a definitive answer based on the info you supplied.
Suggest you start by looking at this article: Why is my query read only

 

[Minty]

The normal issue is probably rogue data somewhere.

If nothing else has genuinely been changed, then the data involved is the only possible cause.

 

[Isskint]

Hi Colin,

I had used that checklist (from memory) before posting. However, I have just gone through again to be sure.
And I found it. One of the source tables in a JOINed field did not have a PK.
How does a table suddenly lose it's PK?

 

[The_Doc_Man]

If this is a shared database, the question is whether someone has corrupted something. Have you tried to Compact & Repair this DB? If it is a production DB, make a backup copy BEFORE you attempt the C & R, just as a safety precaution.

 

[Frothingslosh]

Another thought: is the table a linked table? And did someone relink it just before this started happening?

 

[Isskint]

Hi Doc,

Split FE / BE. BE on company server, FE on users PC's.

However, i did have to do a C & R this morning on the BE. Can the repair operation remove a PK?

 

[Frothingslosh]

However, i did have to do a C & R this morning on the BE. Can the repair operation remove a PK?

I've never heard of that happening before.

 

[The_Doc_Man]

In theory, a C&R can remove anything that is corrupt. The follow-up questions:

1. Did it remove the PK property on a field in a TableDef or did it actually remove the field?

2. If it was only the property that got altered, can you reassert the property?

 

[Isskint]

Hi Doc

Yep, only the PK property was removed. I reinstated it and everything works fine.