By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 1,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Help with rewriting Access UPDATE query to be SQL Server compliant

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Apr 30, 2:55*am, teddysn...@hotmail.com wrote:
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
what's the problem with using a passthrough query ?
Jun 27 '08 #2

P: n/a
On 30 Apr, 11:17, Roger <lesperan...@natpro.comwrote:
On Apr 30, 2:55*am, teddysn...@hotmail.com wrote:
[...]
what's the problem with using a passthrough query ?- Hide quoted text -
The database was developed by a gifted amateur, who more or less
taught herself Access as she went along. I've been asked to do the
upsizing to SQL Server, but I want to keep any modifications to the
front end as simple and close to standard Access as possible.

Edward
Jun 27 '08 #3

P: n/a
"Roger" <le*********@natpro.comwrote
what's the problem with using a passthrough query ?
The usual problem with a passthrough query is that Access/Jet-ACE has its
own dialect of SQL, and the back-end servers all have their own dialects of
SQL. Unless the users go 'way out of their way to adhere to ANSI-standard
or world-wide-standard SQL (and, it is amazing how many, even when their
projects claim "industry-standard", do not) those will not be the same.
Some developers or SQL-addicted-hobbyists may revel in knowing second-nature
all the major / minor / obvious / subtle differences, but typical users
(and, I suspect, typical developers) generally limit their scope to one
"dialect".

Jet and ODBC, in my experience, translate Jet SQL to surprisingly "good",
efficient server SQL when the user creates the Query in Access. It's not as
good as a 'rockstar' DBA or server Developer could do in a world-class
competition, but better than one might expect.

Larry Linson
Microsoft Office Access MVP
Jun 27 '08 #4

P: n/a
On Apr 30, 4:49*am, teddysn...@hotmail.com wrote:
On 30 Apr, 11:17, Roger <lesperan...@natpro.comwrote:
On Apr 30, 2:55*am, teddysn...@hotmail.com wrote:
[...]
what's the problem with using a passthrough query ?- Hide quoted text -

The database was developed by a gifted amateur, who more or less
taught herself Access as she went along. *I've been asked to do the
upsizing to SQL Server, but I want to keep any modifications to the
front end as simple and close to standard Access as possible.

Edward
ok, using access97, I created 2 tables with two fields each (removing
spaces in field names) and this query runs with no problem

tblBookings
transFromId bookingId
1 2

tblRefunds
bookingId movedToNewBkref
1 0
UPDATE tblbookings INNER JOIN tblREFUNDS ON tblbookings.TransFromID =
tblREFUNDS.BookingID SET tblREFUNDS.MOVEDTONEWBKREF = tblbookings!
bookingid
WHERE (((tblREFUNDS.MOVEDTONEWBKREF)=0) AND
((tblbookings.TransFromID) Is Not Null));

after running the query, movedToNewBkref = 2

so I'm not sure why you're getting this error
Jun 27 '08 #5

P: n/a
On Apr 30, 3:55 am, teddysn...@hotmail.com wrote:
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
I think the query you wrote in QA will work in Access if you get rid
of the table aliases 'r' and 'b' or create the aliases using
'as' (e.g. 'tblBookings as b' and 'tblRefunds as r'). The Access
syntax is a little bit different than the TSQL syntax but I don't
recall the exact differences.

Did you recreate your unique indexes on the SQL side? Both tables
being joined must have unique indexes. I do not trust the upsizing
wizards to get this right, if that's what you used to move your data
to SQL server.

Bruce
Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.