Running Oracle SQL in Access

[psyckle]

Hi, I'm attempting to run a simple SQL statement where I am updating fields from selection

SELECT Field
FROM Table
WHERE Table.Field = VAR

UPDATE (SELECT Field
FROM Table
WHERE Table.Field = VAR) TEMP
SET TEMP.Field = NEW_VAR;

The intention is to display the selection as a report and then update the fields. I've used this code thru Oracle but am a bit perplexed with the syntax error in Access? Can anybody suggest where I have made the error, or suggest a way to achieve the report and update?

 

[jrbarnett]

Hi,

This is because with Access, it will be interpreted using the Jet database engine (which underlies access), and it uses a different SQL syntax to Oracle PL/SQL.

If your table is a linked table to Oracle, you can set the query type to pass through in the query design mode, and then Access won't attempt to interpret it - it will pass it through to Oracle and return any results.

John