Recordset is not updateable.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee Stafford - 05 May 2005 21:06 GMT

I have a table that I need to update and I have changed this arrangement
too many times to count. I think this is the closest that I have come yet.
Here is the SQL.

SELECT qa.WONumber,
job.CorrespondingType,
sec.SectionDesc,
[ques.SectionID]&'.'&[ques.QuesNum] AS Line_Number,
ques.Question,
qa.Response
FROM ((((tbl_questions ques
INNER JOIN tbl_section sec
ON ques.sectionid = sec.sectionid)
INNER JOIN TBL_JOB_TYPE job
ON sec.sectionid = job.sectionid)
INNER JOIN tbl_QA_WO_INFO info
ON job.jobtype = info.jobtype)
LEFT OUTER JOIN tbl_QandA qa
ON info.wonumber = qa.wonumber)

Everythingelse is linked and will fill in all but the response field.
Which is the field I want to update. Can someone help me? I have read so
much on updateable queries and I haven't grasped the concept yet.

tia,

Lee

Reply to this Message

Van T. Dinh - 06 May 2005 00:15 GMT

Queries that involve more than 2 Tables are often non-updateable.

Check Access Help topic "Updatable Queries" which explains when a Query is
updateable and gives a few techniques to convert a non-updateable Query to
become updateable.

Signature

HTH
Van T. Dinh
MVP (Access)

> I have a table that I need to update and I have changed this arrangement
> too many times to count. I think this is the closest that I have come yet.
[quoted text clipped - 23 lines]
>
> Lee

Reply to this Message

Jack MacDonald - 06 May 2005 04:06 GMT

Access will make a query non-updateable when it cannot reliably
determine which one of several potential records are intended to be
updated. I have always found it useful to make a COPY of the query,
then delete tables one-by-one to determine exactly which relationship
is causing the non-updateablility (???). From there, it is usually a
matter of examining the indexes to ensure that records are properly
identified with Unique indexes.

>I have a table that I need to update and I have changed this arrangement
>too many times to count. I think this is the closest that I have come yet.
[quoted text clipped - 23 lines]
>
>Lee

**********************
jackmacMACdonald@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Reply to this Message

John Vinson - 06 May 2005 06:28 GMT

>Everythingelse is linked and will fill in all but the response field.
>Which is the field I want to update. Can someone help me? I have read so
>much on updateable queries and I haven't grasped the concept yet.

Is it OBLIGATORY to construct one grand master query to update
everything? Bear in mind that Access is unusual among databases in
that it lets *two* table queries be updateable, not to speak of
five-table queries! It may just be that Access won't LET you make such
a deep query updateable.

Ordinarily if you're doing manual updating, an arrangement of Forms
and Subforms would be preferable. Might that be an option in this
case?

John W. Vinson[MVP]

Reply to this Message