Help with rewriting Access UPDATE query to be SQL Server compliant

[teddysnips@hotmail.com]

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.Tra nsFromID =
tblREFUNDS.Book ingID SET tblREFUNDS.[MOVED TO NEW BKREF] = tblbookings!
bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null));

I tried rewriting it as follows, with the same problem:

UPDATE tblRefunds, tblBookings
SET [MOVED TO NEW BKREF] = tblbookings.boo kingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null)) AND (tblREFUNDS.Boo kingID =
tblbookings.Tra nsFromID );

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

[Roger]

Re: Help with rewriting Access UPDATE query to be SQL Servercompliant

On Apr 30, 2:55 am, teddysn...@hotm ail.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.Tra nsFromID =
tblREFUNDS.Book ingID SET tblREFUNDS.[MOVED TO NEW BKREF] = tblbookings!
bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null));
>
I tried rewriting it as follows, with the same problem:
>
UPDATE tblRefunds, tblBookings
SET [MOVED TO NEW BKREF] = tblbookings.boo kingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null)) AND (tblREFUNDS.Boo kingID =
tblbookings.Tra nsFromID );
>
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 ?

[teddysnips@hotmail.com]

Re: Help with rewriting Access UPDATE query to be SQL Servercompliant

On 30 Apr, 11:17, Roger <lesperan...@na tpro.comwrote:

On Apr 30, 2:55 am, teddysn...@hotm ail.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

[Larry Linson]

Re: Help with rewriting Access UPDATE query to be SQL Server compliant

"Roger" <lesperancer@na tpro.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

[Roger]

Re: Help with rewriting Access UPDATE query to be SQL Servercompliant

On Apr 30, 4:49 am, teddysn...@hotm ail.com wrote:

On 30 Apr, 11:17, Roger <lesperan...@na tpro.comwrote:
>

On Apr 30, 2:55 am, teddysn...@hotm ail.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.Tra nsFromID =
tblREFUNDS.Book ingID SET tblREFUNDS.MOVE DTONEWBKREF = tblbookings!
bookingid
WHERE (((tblREFUNDS.M OVEDTONEWBKREF) =0) AND
((tblbookings.T ransFromID) Is Not Null));

after running the query, movedToNewBkref = 2

so I'm not sure why you're getting this error

[Bruce]

Re: Help with rewriting Access UPDATE query to be SQL Servercompliant

On Apr 30, 3:55 am, teddysn...@hotm ail.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.Tra nsFromID =
tblREFUNDS.Book ingID SET tblREFUNDS.[MOVED TO NEW BKREF] = tblbookings!
bookingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null));
>
I tried rewriting it as follows, with the same problem:
>
UPDATE tblRefunds, tblBookings
SET [MOVED TO NEW BKREF] = tblbookings.boo kingid
WHERE (((tblREFUNDS.[MOVED TO NEW BKREF])=0) AND
((tblbookings.T ransFromID) Is Not Null)) AND (tblREFUNDS.Boo kingID =
tblbookings.Tra nsFromID );
>
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