syntax error for update sql in access

Question

I got a syntax error when I ran a sql update in access 2013. The targeted table saves inventory balance data. to update the balance, the original data will subtract the consumption amount resulted from production activities. I use a query to get the sum of consumption. My codes are as follows:

Private Sub Command4_Click()
CurrentDb.Execute ("UPDATE tbl_Current_Stock As o" & _
                  “Inner join preview_Of_Raw_Material_Consumption As p” & _
                  “On o.Raw_Material =p.[Ingredient/Packaging material]” & _
                 "Set o.Stock_Level = o.Stock_Level- p.SumOfConsumption" )
 End Sub

EDIT - Add current code that isn't working:

CurrentDb.Execute ("UPDATE tbl_Current_Stock As o Inner join preview_Of_Raw_Material_Consumption As p On o.[Raw_Material] = p.[Ingredient/Packaging material]Set o.[Stock_Level] = o.[Stock_Level]- p.SumOfConsumption from o p")

[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Sep 18 '16 at 22:35
Update queries in MS Access should not have a`FROM` clause. Additionally, update queries should be [updateable](http://allenbrowne.com/ser-61.html) which includes not having aggregate queries in underlying join objects. I bring this up because I see a `SumOfConsumption` column. — Parfait, Sep 19 '16 at 01:36

score 0 · Answer 1 · answered Sep 18 '16 at 16:07

0

Your query looks something like this:

UPDATE tbl_Current_Stock As oInner join preview_Of_Raw_Material_Consumption As pOn o.Raw_Material =p.[Ingredient/Packaging material]Set o.Stock_Level = o.Stock_Level- p.SumOfConsumption

Can you spot the problem? Yes, you have run-on lines in the query. If you print the query string before running it, then the problem is obvious.

The solution is to put appropriate spaces in the string:

CurrentDb.Execute ("UPDATE tbl_Current_Stock As o" & _
                   " Inner join preview_Of_Raw_Material_Consumption As p” & _
                   " On o.Raw_Material = p.[Ingredient/Packaging material]” & _
                   " Set o.Stock_Level = o.Stock_Level- p.SumOfConsumption" )

answered Sep 18 '16 at 16:07

Gordon Linoff

1,242,037
58
646
786

Thank you Gordon, As you suggested I rearranged my code string in one line like CurrentDb.Execute ("UPDATE tbl_Current_Stock As o Inner join preview_Of_Raw_Material_Consumption As p On o.Raw_Material = p.[Ingredient/Packaging material]Set o.Stock_Level = o.Stock_Level- p.SumOfConsumption") but I got an error message as "too few parameters Expect 31". – jiangzhou He Sep 18 '16 at 19:04
So I think maybe I need to add the from statement in the string and change my codes to CurrentDb.Execute ("UPDATE tbl_Current_Stock As o Inner join preview_Of_Raw_Material_Consumption As p On o.[Raw_Material] = p.[Ingredient/Packaging material]Set o.[Stock_Level] = o.[Stock_Level]- p.SumOfConsumption from o p"). This time I got a syntax error (missing operator). – jiangzhou He Sep 18 '16 at 19:09
1

Put your edits in your question with edit comments - like I did above. Take some time to look at your attempts. Right now you don't have a space before Set – dbmitch Sep 18 '16 at 22:27

score 0 · Answer 2 · answered Sep 19 '16 at 17:24

Thank you Gordon. I finaly found the real problem of my code. I would like to share my experiences and it maybe helpful for others who are bothered by the same or similar problem. As I mentioned in my first question post, one of my update source comes from a query. this query is a sum of individual consumption and it is "unupdatable". By searching online I use a temporal table as the data source and the code runs smoothly.

CurrentDb.Execute ("Update tbl_Current_Stock As o Inner join tbl_Temp_Raw_Material_Consumption As s On o.Raw_Material = s.[Ingredient/Packaging material] Set o.Stock_Level = o.Stock_Level - s.Consumption")

Here tbl_Temp_Raw_Material_Consumption is the make table query of the original query.

syntax error for update sql in access

2 Answers2