CODEWITHSUNDEEP
sqlvbams-access-2013
RedHat
RedHat

Reputation: 205

VBA - Missing Operator SQL Query Error

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 "

enter image description here

Upvotes: 0

Views: 257

Answers (2)

r.paul Ǿ
r.paul Ǿ

Reputation: 111

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"

Upvotes: 0

cha
cha

Reputation: 10411

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

Upvotes: 1

Related Questions