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

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
 
M

MNF

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filtering causes "The column prefix '..' does not match with a table name or alias used in the query 1
Recordset not Updatable. 1
"this recordset is not updatable" problem in Access 2003 3
Query not updatable - advise on how to change it 4
Updatable Query (trying to edit records) 8
Error: Operation must use an updatable query 2
Query is not updatable - 3
Update Query Fails due to Source table not being Updatable 2

Top