"This Recordset is not updateable"

I cannot make data changes to a query or its attached form.  When I attempt 
to change a field there is a sound and a message appears in the lower left, 
"This Recordset is not updateable."
How did this come about and what do I need to do to be able to update data 
again? 

0
jayC
3/1/2008 8:06:04 PM
access 16762 articles. 3 followers. Follow

4 Replies
706 Views

Similar Articles

[PageSpeed] 18

Jay,

There are a number of factors that determine whether a query's data set 
is updateable.  For example, the nature of the relationships between the 
tables in the query, the presence of aggregate functions in the query, 
etc.  If you would care to post the SQL view of the query here, someone 
will be able to point to what is happening in this instance.

-- 
Steve Schapel, Microsoft Access MVP

jayC wrote:
> I cannot make data changes to a query or its attached form.  When I 
> attempt to change a field there is a sound and a message appears in the 
> lower left, "This Recordset is not updateable."
> How did this come about and what do I need to do to be able to update 
> data again?
0
Steve
3/1/2008 8:21:33 PM
SELECT [W208-02JAN].Field2, [W208-02JAN].Field1, [W208-02JAN].InDate, 
[W208-02JAN].[CUSTOMER NAME], [W208-02JAN].ADDRESS, [W208-02JAN].CITY, 
[W208-02JAN].ATTENTION, [W208-02JAN].Code, [W208-02JAN].[ACCT NO], 
[W208-02JAN].[Cust Prop No], [W208-02JAN].[JOB LOCATION], 
[W208-02JAN].PRICE, [W208-02JAN].[CUST NO], 
IIf(IsNull([1tot]),[PRICE],IIf(IsNull([2tot]),[PRICE]+[1tot],IIf(IsNull([3tot]),[PRICE]+[1tot]+[2tot],IIf(IsNull([4tot]),[PRICE]+[1tot]+[2tot]+[3tot],IIf(IsNull([5tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot],IIf(IsNull([6tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot],IIf(IsNull([7tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot],[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot]+[7tot]))))))) 
AS TOT, [W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1, 
[W208-02JAN].x1, IIf(IsNull([x1]),[QTY1]*[EA1],[x1]) AS 1tot, 
[W208-02JAN].QTY2, [W208-02JAN].DES2, [W208-02JAN].EA2, [W208-02JAN].x2, 
IIf(IsNull([x2]),[QTY2]*[EA2],[x2]) AS 2tot, [W208-02JAN].QTY3, 
[W208-02JAN].DES3, [W208-02JAN].EA3, [W208-02JAN].x3, 
IIf(IsNull([x3]),[QTY3]*[EA3],[x3]) AS 3tot, [W208-02JAN].QTY4, 
[W208-02JAN].DES4, [W208-02JAN].EA4, [W208-02JAN].x4, 
IIf(IsNull([x4]),[QTY4]*[EA4],[x4]) AS 4tot, [W208-02JAN].QTY5, 
[W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].x5, 
IIf(IsNull([x5]),[QTY5]*[EA5],[x5]) AS 5tot, [W208-02JAN].QTY6, 
[W208-02JAN].DES6, [W208-02JAN].EA6, [W208-02JAN].x6, 
IIf(IsNull([x6]),[QTY6]*[EA6],[x6]) AS 6tot, [W208-02JAN].QTY7, 
[W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].x7, 
IIf(IsNull([x7]),[QTY7]*[EA7],[x7]) AS 7tot, [W208-02JAN].QTY8, 
[W208-02JAN].DES8, [W208-02JAN].EA8, [W208-02JAN].QTY9, [W208-02JAN].DES9, 
[W208-02JAN].EA9, [W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10, 
[W208-02JAN].TOT10 AS Expr1, [W208-02JAN].QTY11, [W208-02JAN].DES11, 
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12, 
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12, 
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13, 
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14, 
[W208-02JAN].EA14, [W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15
FROM [W208-02JAN]
GROUP BY [W208-02JAN].Field2, [W208-02JAN].Field1, [W208-02JAN].InDate, 
[W208-02JAN].[CUSTOMER NAME], [W208-02JAN].ADDRESS, [W208-02JAN].CITY, 
[W208-02JAN].ATTENTION, [W208-02JAN].Code, [W208-02JAN].[ACCT NO], 
[W208-02JAN].[Cust Prop No], [W208-02JAN].[JOB LOCATION], 
[W208-02JAN].PRICE, [W208-02JAN].[CUST NO], 
IIf(IsNull([1tot]),[PRICE],IIf(IsNull([2tot]),[PRICE]+[1tot],IIf(IsNull([3tot]),[PRICE]+[1tot]+[2tot],IIf(IsNull([4tot]),[PRICE]+[1tot]+[2tot]+[3tot],IIf(IsNull([5tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot],IIf(IsNull([6tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot],IIf(IsNull([7tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot],[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot]+[7tot]))))))), 
[W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1, [W208-02JAN].x1, 
IIf(IsNull([x1]),[QTY1]*[EA1],[x1]), [W208-02JAN].QTY2, [W208-02JAN].DES2, 
[W208-02JAN].EA2, [W208-02JAN].x2, IIf(IsNull([x2]),[QTY2]*[EA2],[x2]), 
[W208-02JAN].QTY3, [W208-02JAN].DES3, [W208-02JAN].EA3, [W208-02JAN].x3, 
IIf(IsNull([x3]),[QTY3]*[EA3],[x3]), [W208-02JAN].QTY4, [W208-02JAN].DES4, 
[W208-02JAN].EA4, [W208-02JAN].x4, IIf(IsNull([x4]),[QTY4]*[EA4],[x4]), 
[W208-02JAN].QTY5, [W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].x5, 
IIf(IsNull([x5]),[QTY5]*[EA5],[x5]), [W208-02JAN].QTY6, [W208-02JAN].DES6, 
[W208-02JAN].EA6, [W208-02JAN].x6, IIf(IsNull([x6]),[QTY6]*[EA6],[x6]), 
[W208-02JAN].QTY7, [W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].x7, 
IIf(IsNull([x7]),[QTY7]*[EA7],[x7]), [W208-02JAN].QTY8, [W208-02JAN].DES8, 
[W208-02JAN].EA8, [W208-02JAN].QTY9, [W208-02JAN].DES9, [W208-02JAN].EA9, 
[W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10, 
[W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11, 
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12, 
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12, 
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13, 
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14, 
[W208-02JAN].EA14, [W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15
HAVING ((([W208-02JAN].InDate)="208-03"))
ORDER BY [W208-02JAN].Code, [W208-02JAN].[ACCT NO], [W208-02JAN].[Cust Prop 
No];




"Steve Schapel" <schapel@mvps.org.ns> wrote in message 
news:%23ni3Yn9eIHA.1168@TK2MSFTNGP02.phx.gbl...
> Jay,
>
> There are a number of factors that determine whether a query's data set is 
> updateable.  For example, the nature of the relationships between the 
> tables in the query, the presence of aggregate functions in the query, 
> etc.  If you would care to post the SQL view of the query here, someone 
> will be able to point to what is happening in this instance.
>
> -- 
> Steve Schapel, Microsoft Access MVP
>
> jayC wrote:
>> I cannot make data changes to a query or its attached form.  When I 
>> attempt to change a field there is a sound and a message appears in the 
>> lower left, "This Recordset is not updateable."
>> How did this come about and what do I need to do to be able to update 
>> data again? 

0
jayC
3/1/2008 9:30:51 PM
Hi Jay,

All I can say after seeing that is Oh.....My.....God!
One reason this query is not updateable is that it includes a Group By clause:

FROM [W208-02JAN]
GROUP BY ............

There may be other reasons as well. Take a look at Access MVP Allen Browne's 
list, available here:

    Why is my query read-only?
    http://allenbrowne.com/ser-61.html

I would start investigating methods of simplifying this query. It looks to 
me like a maintanance nightmare. The fact that you require all of the IIF 
functions strongly suggest that your data may not be properly normalized.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"jayC" wrote:

<snipped hugemongous SQL statement>
0
Utf
3/1/2008 10:25:00 PM
"jayC" <test@rcn.com> wrote:

>SELECT [W208-02JAN]

What kind of table is that?

>SELECT [W208-02JAN].Field2, [W208-02JAN].Field1, 

Field2 and Field1 are not very descriptive names.

>[W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1, 

Why do you have repeating field names?  QTY1, QTY2, DES1, DES2 and so
forth?  This is usually an indicator of a badly designed table. This
data should be normalized.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
0
Tony
3/3/2008 1:13:49 AM
Reply:

Similar Artilces:

"This Recordset is not updateable"
I cannot make data changes to a query or its attached form. When I attempt to change a field there is a sound and a message appears in the lower left, "This Recordset is not updateable." How did this come about and what do I need to do to be able to update data again? Jay, There are a number of factors that determine whether a query's data set is updateable. For example, the nature of the relationships between the tables in the query, the presence of aggregate functions in the query, etc. If you would care to post the SQL view of the query here, someone will be able...

This recordset is not updateable
I have a frontend database linked to a backend database on a central server. I have a form that has been working correctly for several months now and suddenly i cannot change anything and it says in the status bar at the lower left side of access "this recordset is not updateable". I have tried compacting and repairing the backend database and still no luck. The Front end Database is on 5 computers in the office and they all have this same issue. Can this be fixed? Joshua, Are you able to tell us please what is the Record Source of this troublesome form? If it's a q...