Run-time error on SQL UPDATE in VBA

Question

I am trying to make some code, which updates a row, if the item already exists. It looks like this:

updateStr = "UPDATE platinum_paste SET ([aluminium], [calcium], [chrome], [iron], [lead], [silicon], [zirconium]) VALUES (" & aluminium & "," & _
                                                                                                                              calcium & "," & _
                                                                                                                              chrome & "," & _
                                                                                                                              iron & "," & _
                                                                                                                              lead & "," & _
                                                                                                                              silicon & "," & _
                                                                                                                              zirconium & _
                                                                                                                              ") WHERE [lot_number] = " & lotNumber

Debug.Print updateStr

If MsgBox("Item already exists. Do you want to update its details?", vbYesNo) = vbYes Then
            objMyConn.Execute updateStr, dbFailOnError

Else
            'Do nothing

End If

When I try to run it, clicking "Yes" in the MsgBox will result in an error:

Run-time error '-2147217900 (80040e14)':

Automation error

Can anyone tell me what I am doing wrong?I have tried to add all possible debug functions I can think of. The database connection works fine for SELECT and INSERT. This is my INSERT string, which works just fine:

insertStr = "INSERT INTO platinum_paste ([lot_number], [aluminium], [calcium], [chrome], [iron], [lead], [silicon], [zirconium]) VALUES (" & lotNumber & "," & _
                                                                                                                                                 aluminium & "," & _
                                                                                                                                                 calcium & "," & _
                                                                                                                                                 chrome & "," & _
                                                                                                                                                 iron & "," & _
                                                                                                                                                 lead & "," & _
                                                                                                                                                 silicon & "," & _
                                                                                                                                                 zirconium & ")"

You should use parameterized queries instead of dumping user input directly in the query string. That will probably fix your problem. — Gordon Linoff
– Gordon Linoff, Commented Oct 22, 2015 at 11:54
I will try to look into that, however it still does not explain why my method does not work... It is almost identical to my INSERT string, which works just fine. — Jakob Busk Sørensen
– Jakob Busk Sørensen, Commented Oct 22, 2015 at 11:57
When you say it is 'almost identical' what are the differences? — Bmo
– Bmo, Commented Oct 22, 2015 at 12:00

Verzweifler · Accepted Answer · 2015-10-22 12:06:37Z

3

The Syntax of your UPDATE-String is wrong. You need to write

 UPDATE table_name
 SET column1=value1,column2=value2,...
 WHERE some_column=some_value;

For further information, have a look at http://www.w3schools.com/sql/sql_update.asp

answered Oct 22, 2015 at 12:06

Verzweifler

9406 silver badges19 bronze badges

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

3 Comments

Bmo Over a year ago

Wow, seems obvious now. Clearly, I need more coffee.

Jakob Busk Sørensen Over a year ago

Thanks, that worked. Not used to working much with UPDATE in SQL. I still don't get why none of my Debug attempts gave me anything useful...?

Verzweifler Over a year ago

The Debug.Print-Messages I see in your code just print out the SQL String. They can't test its usefullness...

Collectives™ on Stack Overflow

Run-time error on SQL UPDATE in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related