更新查询的MS Access SQL错误

working on linking data between a SQL Server Database and MS Access. Right now someone is manually calculating Data from a SQL Database report and entering this into Access to run other reports within Access.

致力于在SQL Server数据库和MS Access之间链接数据。现在有人手动计算SQL数据库报告中的数据,并将其输入Access以在Access中运行其他报告。

I have created a pass through query to pull the relevant information into an Access Table from the SQL Database( all working nicely )

我创建了一个pass through查询,将相关信息从SQL数据库中提取到一个Access Table中(一切正常)

Now I need to update the existing Access Tables with Data retrieved from the SQL pass through. I have tried a number of different queries all fussing at me for various reasons. Here is an example of the latest query that will get me what I need. This works if I setup a Sandbox in SQL Server and run it MSSQL Management Studio, but will not work in access

现在我需要使用从SQL传递中检索的数据更新现有的Access Table。由于各种原因,我尝试了许多不同的查询。以下是最新查询的示例,它将为我提供所需内容。如果我在SQL Server中设置沙箱并将其运行到MSSQL Management Studio,但无法在访问中工作,则此方法有效

UPDATE JT 
SET    JT.ContractAmt = SBD.TotalSum 
FROM   JobTable_TEST AS JT
INNER JOIN (
             SELECT Sum( Main.amt ) as TotalSum, Main.job 
             FROM  Main 
             GROUP BY Main.job
           ) AS SBD 
ON SBD.job = JT.JobNumber 

In Access the Above Generates the following error "Syntax error( missing operator) in query expression.

在Access the Above中生成以下错误“查询表达式中的语法错误(缺少运算符)。

---

Updating following attempt at using SQL Passthrough to run the update Query.

更新以下尝试使用SQL Passthrough运行更新查询。

I updated my Query to do this directly from a Passthrough SQL Statement as suggested and get the following error.

我更新了我的查询,直接从建议的Passthrough SQL语句执行此操作,并获得以下错误。

ODBC--call failed.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'TableName'.(#208)

[Microsoft] [SQL Server Native Client 11.0] [SQL Server]无效的对象名称“TableName”。(#208)

Here is what the pass through query i used looked like.

这是我使用的传递查询看起来像。

UPDATE AccessTable
SET AccessTable.amt = SQLResult.Total
FROM TableName AS AccessTable
    INNER JOIN ( SELECT SUM( SQLTableA.amt) as Total, SQLTableA.job
                 FROM SQLTableA 
                 LEFT OUTER JOIN SQLTableB ON (SQLTableA.company = SQLTableB.company) 
                                            AND (SQLTableA.job = SQLTableB.job) 
                 GROUP BY SQLTableA.job
                ) AS SQLResult
ON SQLResult.job = AccessTable.JobNum

hopefully that better describes where my tables are located and how my update needs to happen, and maybe someone can point out how this is wrong or if it will even work this way.

希望更好地描述我的表所在的位置以及我的更新需要如何发生,也许有人可以指出这是错误的,或者它是否会以这种方式工作。

---

Any suggestions would be greatly appreciated

任何建议将不胜感激

1 个解决方案

#1

It appears your subquery, aliased as SBD, is missing a job_no column. Therefore you aren't going to be able to join on it.

看起来您的子查询(别名为SBD)缺少job_no列。因此,您无法加入它。