MS Access SQL error with Update Query

Question

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.

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

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

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.

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

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

ODBC--call failed.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '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

Consider using an Access pass-through query to execute that UPDATE statement at the server. — HansUp
– HansUp, Commented Apr 24, 2015 at 23:18
I like this idea, but seem to be running into problems. When i modified this query to update the Access Table from the SQL Passthrough Query it cannot identify the Access Table. Updating question with query and error. — Nathan
– Nathan, Commented Apr 27, 2015 at 20:25
It seems I misunderstood what you told us. I meant execute UPDATE JT ... as a pass-through query since it works there. I didn't understand where an Access table is involved. Sorry. — HansUp
– HansUp, Commented Apr 28, 2015 at 0:05

Randy Minder · Accepted Answer · 2015-04-24 21:07:18Z

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.

answered Apr 24, 2015 at 21:07

Randy Minder

48.8k56 gold badges222 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nathan Over a year ago

That was just a typo on my part, apologies. I've updated the SQL in the post

Collectives™ on Stack Overflow

MS Access SQL error with Update Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related