Having upsized my client's back-end DB to SQL Server, the following
query does not work ("Operation must use an updateable query").
UPDATE tblbookings INNER JOIN tblREFUNDS ON tblbookings.TransFromID =
tblREFUNDS.BookingID SET tblREFUNDS.[MOVED TO NEW BKREF] = tblbookings!
bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.TransFromID) Is Not Null));
I tried rewriting it as follows, with the same problem:
UPDATE tblRefunds, tblBookings
SET [MOVED TO NEW BKREF] = tblbookings.bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.TransFromID) Is Not Null)) AND (tblREFUNDS.BookingID =
tblbookings.TransFromID );
In SQL Server's QA the following flavour works:
UPDATE tblREFUNDS
SET tblREFUNDS.[MOVED TO NEW BKREF] = b.bookingid
FROM tblREFUNDS r INNER JOIN tblBookings b ON b.TransFromID =
r.BookingID
WHERE (((r.[MOVED TO NEW BKREF])=0) AND ((b.TransFromID) Is Not
Null));
but if I paste this into an Access query I get a "Syntax error
(missing Operator) in b.bookingid
FROM tblREFUNDS r INNER JOIN tblBookings b ON b.TransFromID =
r.BookingID"
I am (and more importantly my client is) getting increasingly
frustrated with this. One obvious method would be to move the query
to a Stored Procedure, but there are various reasons why I don't want
to do this (mainly to do with the client being able to maintain the
system).
Has anyone any thoughts on how to rewrite the original query as an
Access query without creating a Passthrough Query?
Many thanks
Edward
