Access update query syntax error (missing operator)

Question

This appears to be a fairly common problem in Access, but after researching multiple threads and trying all types of variations, I still can't find a solution for my problem.

Here is a simple Select query that runs just fine which pulls data from an Access table and a linked Oracle table:

SELECT a.WELL_UWI, b.MAIN_FORM
FROM  (SELECT WELL_UWI, MAIN_FORM
      FROM tmp_form) AS b
INNER JOIN eauser_nfx_hz_well_summary AS a
ON b.WELL_UWI = a.WELL_UWI;

I modified it to be an update query to update the linked Oracle table from data in the Access table as follows:

UPDATE a
SET a.MAIN_HZ_FM = b.MAIN_FORM
FROM  (SELECT WELL_UWI, MAIN_FORM
      FROM tmp_form) AS b
INNER JOIN eauser_nfx_hz_well_summary AS a
ON b.WELL_UWI = a.WELL_UWI;

I end up with this message:

Syntax error (missing operator) in query expression 'b.MAIN_FORM FROM (SELECT WELL_UWI, MAIN_FORM FROM tmp_form) AS b INNER JOIN eauser_nfx_hz_well_summary AS a ON b.WELL_UWI = a.WELL_UWI

Any idea what I'm missing?

Thanks!

All, just an FYI, I did find an answer to this problem. It had to do with a bug in the Oracle driver I was using. The answer is detailed in my other post found here: stackoverflow.com/questions/49515633/… — Heather

Erik A Erik A · Accepted Answer · 2018-03-28T16:30:14

Access has a different syntax for updates. All tables are specified directly after the UPDATE keyword, and there's no FROM:

UPDATE (SELECT WELL_UWI, MAIN_FORM
      FROM tmp_form) AS b
INNER JOIN eauser_nfx_hz_well_summary AS a
ON b.WELL_UWI = a.WELL_UWI
SET a.MAIN_HZ_FM = b.MAIN_FORM;

Note that in Access, the full query (including all subqueries) needs to be updateable. You can't specify you're only updating a specific table.

When you have a problem with locks or non-updateable tables, you can often use a DLookUp to avoid these problems:

UPDATE eauser_nfx_hz_well_summary AS a
SET a.MAIN_HZ_FM = DLookUp("MAIN_FORM", "tmp_form", "WELL_UWI = '" & a.WELL_UWI & "'")

Access update query syntax error (missing operator)

2 Answers