VBA - 缺少运算符SQL查询错误

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.

我正在尝试使用MS Access在MS Access中使用内部联接更新表，但我已经在SQL Server Management Studio中测试了SQL Update语句并且它正在运行。结果我收到了错误，见下文。

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 "

2 个解决方案

#1

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

在Access中，与SQL Server相比，具有联接的更新具有不同的语法。正确的语法是：

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:

顺便说一句，早期的Access版本不喜欢一个接一个跟随的INNER JOINS。他们使用嵌套连接语法：

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

#2

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

这个想法基本上是你必须以嵌套的方式进行JOIN。见下面正确的一个。

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"

#1