This Recordset is not updateable error.

[CGSB]

My form uses the SQL statement below to populate itsfields:

SELECT course.course_name, candidate_course.status,   
       candidate_course.year, candidate_course.quarter, 
       candidate_course.file_no
FROM course LEFT JOIN candidate_course 
ON (course.course_id = candidate_course.course_id 
AND candidate_course.file_no = '77777')
WHERE course.set="1
ORDER BY course.course_name;

When I try to update one of the fields, a message appears saying "This Recordset is not updateable".

My question is, how can I change the above SQL statement so that it allows my Recordset to be updateable.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Note: your code don't seem to be JetSQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CGSB]

I don't completely understand. My query works fine when I take out the clause:

AND candidate_course.file_no = '77777'

But as soon as that clause is in there, it's not updateable anymore.

I think it's because it's a many-to-many query (I want many results from many tables).

Is there a different kind of SQL statement that I could use?

 

[PizMac]

2 things - I suspect you need to make course_id on the course table a primAry key and then it will be updateable but also - surely the file_no=7777 should not be part of the JOIN but part of the WHERE clause as follows????

SELECT course.course_name, candidate_course.status, candidate_course.year, candidate_course.quarter, candidate_course.file_no
FROM candidate_course INNER JOIN course ON candidate_course.course_id = course.course_id
WHERE candidate_course.file_no="7777" AND course.set="1"
ORDER BY course.course_name;

 

[fneily]

See:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp