Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-13-2018, 05:58 AM   #1
Isskint
Slowly Developing
 
Isskint's Avatar
 
Join Date: Apr 2012
Location: Wiltshire, UK
Posts: 1,301
Thanks: 124
Thanked 311 Times in 304 Posts
Isskint will become famous soon enough
Suddenly non updateable recordset

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?

__________________
Isskint, i get satisfaction out of helping others
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Success is the sum of small efforts, repeated day in and day out
Isskint is offline   Reply With Quote
Old 12-13-2018, 06:02 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,607
Thanks: 102
Thanked 2,263 Times in 2,087 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Suddenly non updateable recordset

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Isskint (12-13-2018)
Old 12-13-2018, 06:16 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,066
Thanks: 143
Thanked 1,633 Times in 1,605 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Suddenly non updateable recordset

The normal issue is probably rogue data somewhere.

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

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Isskint (12-13-2018)
Old 12-13-2018, 06:42 AM   #4
Isskint
Slowly Developing
 
Isskint's Avatar
 
Join Date: Apr 2012
Location: Wiltshire, UK
Posts: 1,301
Thanks: 124
Thanked 311 Times in 304 Posts
Isskint will become famous soon enough
Re: Suddenly non updateable recordset

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?
__________________
Isskint, i get satisfaction out of helping others
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Success is the sum of small efforts, repeated day in and day out
Isskint is offline   Reply With Quote
Old 12-13-2018, 06:43 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,103
Thanks: 69
Thanked 1,372 Times in 1,264 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Suddenly non updateable recordset

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Isskint (12-13-2018)
Old 12-13-2018, 06:48 AM   #6
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Suddenly non updateable recordset

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
Isskint (12-13-2018)
Old 12-13-2018, 06:49 AM   #7
Isskint
Slowly Developing
 
Isskint's Avatar
 
Join Date: Apr 2012
Location: Wiltshire, UK
Posts: 1,301
Thanks: 124
Thanked 311 Times in 304 Posts
Isskint will become famous soon enough
Re: Suddenly non updateable recordset

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?

__________________
Isskint, i get satisfaction out of helping others
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Success is the sum of small efforts, repeated day in and day out
Isskint is offline   Reply With Quote
Old 12-13-2018, 06:49 AM   #8
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,144
Thanks: 81
Thanked 442 Times in 402 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Suddenly non updateable recordset

Quote:
Originally Posted by Isskint View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
Isskint (12-13-2018)
Old 12-13-2018, 08:02 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,103
Thanks: 69
Thanked 1,372 Times in 1,264 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Suddenly non updateable recordset

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?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Isskint (12-13-2018)
Old 12-13-2018, 08:31 AM   #10
Isskint
Slowly Developing
 
Isskint's Avatar
 
Join Date: Apr 2012
Location: Wiltshire, UK
Posts: 1,301
Thanks: 124
Thanked 311 Times in 304 Posts
Isskint will become famous soon enough
Re: Suddenly non updateable recordset

Hi Doc

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

__________________
Isskint, i get satisfaction out of helping others
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Success is the sum of small efforts, repeated day in and day out
Isskint is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Why would my Access 2007 query suddenly become not updateable? ojeffery Queries 1 07-02-2012 09:26 AM
recordset not updateable kdt Queries 3 04-09-2006 11:36 AM
Recordset not updateable? RahelWalther Forms 5 03-04-2005 07:59 AM
How to set a RecordSet to be updateable mmitchell Modules & VBA 0 07-28-2004 12:04 PM
Recordset Not Updateable DBL Queries 3 09-10-2002 01:30 PM




All times are GMT -8. The time now is 01:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World