Help with rewriting Access UPDATE query to be SQL Server compliant
- From: teddysnips@xxxxxxxxxxx
- Date: Wed, 30 Apr 2008 01:55:17 -0700 (PDT)
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
.
- Follow-Ups:
- Prev by Date: Re: Help Needed With Format Of Query Expression
- Next by Date: Free YouTube Video Downloader Software
- Previous by thread: report and sub reports
- Next by thread: Re: Help with rewriting Access UPDATE query to be SQL Server compliant
- Index(es):
Relevant Pages
|
