VBA - Missing Operator SQL Query Error

Question

I'm trying to update the table with inner join using VBA in MS Access but I've tested the SQL Update statement in SQL Server Management Studio and it's working. I'm getting an error as a result, see below.

Appreciate any help.

SQL = "Update A set A.RevBillCtrl = 8 from dbo_tblMain A inner join dbo_tblPlateNo as B ON B.PNC = A.PLC inner join dbo_tblSubcons as C on B.SCC = C.SCC "

Answer 1

In Access the Update with joins has a different syntax comparing to SQL Server. The correct syntax will be:

Update dbo_tblMain AS A 
inner join dbo_tblPlateNo as B on B.PNC = A.PLC 
inner join dbo_tblSubcons as C on B.SCC = C.SCC
set A.RevBillCtrl = 8 

BTW, earlier Access versions did not like the INNER JOINS that follow one after another. They used a nested joins syntax:

Update dbo_tblMain AS A 
inner join (dbo_tblPlateNo as B
  inner join (dbo_tblSubcons as C 
  on B.SCC = C.SCC)
on B.PNC = A.PLC)
set A.RevBillCtrl = 8 

Answer 2

The idea basically is that you have to do the JOINs in a nested way. See below correct one.

Sql = "UPDATE dbo_tblMain
      INNER JOIN (dbo_tblPlateNo 
       INNER JOIN dbo_tblSubCons 
       ON (dbo_tblPlateNo.SCC = dbo_tblSubCons.SCC)) 
      ON (dbo_tblMain.PLC = dbo_tblPlateNo.PNC) 
      SET dbo_tblMain.RevBillCtrl = 8"