"This recordset is not updatable" error in multi-table query

  • Thread starter Thread starter MNF
  • Start date Start date
M

MNF

I have a query that joins more than one table with one UniqueTable and
referenced table.
The primary keys are set correctly and UniqueTable on the form is set.
However MS Access ADP shows "This recordset is not updatable" and
doesn't allow to change columns of the unique table.
The SQL query that causes the error is the following:

SELECT PickSlip.PatronID, PickSlip.CourseNo, PickSlip.BookID,
PickSlip.DateGen, PickSlip.Qty, PickSlip.DateShipped, PickSlip.Due,
AUTHORITY_COURSE.Course AS Course
FROM PickSlip INNER JOIN
AUTHORITY_COURSE ON PickSlip.CourseNo =
AUTHORITY_COURSE.CourseNo

PickSlip-is an unique table with primary key (PatronID, CourseNo) and
AUTHORITY_COURSE is a lookup table with primary key CourseNo.

I found, that if I don't output columns from lookup table (ie
AUTHORITY_COURSE.Course) recordset is updatable, even if I still have
joined table. In other words the following SQL is updatable:

SELECT PickSlip.PatronID, PickSlip.CourseNo, PickSlip.BookID,
PickSlip.DateGen, PickSlip.Qty, PickSlip.DateShipped, PickSlip.Due,
'HARD-CODED VALUE' AS Course
FROM PickSlip INNER JOIN
AUTHORITY_COURSE ON PickSlip.CourseNo =
AUTHORITY_COURSE.CourseNo

Any ideas how to solve the problem?

Thanks,
Michael Freidgeim
 
Thanks to the discussion in
http://www.utteraccess.com/forums/access/access584074.html
I figured out how to make the recordset updatable.
I have to specify in SELECT statement key columns from the all tables,
not only TableUnigue.
I didn't see, that it's a requirement in any MS Access documentation.
Ideally MS Access should show more meaningful message.


In my case when I've added AUTHORITY_COURSE.CourseNo to the select, it
become updateable.
ie
SELECT PickSlip.PatronID, PickSlip.CourseNo, PickSlip.BookID,
PickSlip.DateGen, PickSlip.Qty, PickSlip.DateShipped, PickSlip.Due,
AUTHORITY_COURSE.Course AS Course,
AUTHORITY_COURSE.CourseNo as RefTableKey
FROM PickSlip INNER JOIN
AUTHORITY_COURSE ON PickSlip.CourseNo =
AUTHORITY_COURSE.CourseNo


Michael Freidgeim
 
Back
Top