Running a SQL Update Query in Excel VBA

[Guest]

I am using VBA in Excel to connect to a SQL server and retreve data. That
is working. The problem is that I want to run an Update query to update one
line in the database and it isn't working. Below is an example of the code I
am using.


Dim cmdCommand As New ADODB.Command
Dim recSet As New ADODB.Recordset
Dim cn As New ADODB.Connection
cmdCommand.ActiveConnection = cn
strSQLCommand = "UPDATE Materials.ReportName = test Where
materials.MaterialID = 5"
cmdCommand.CommandText = strSQLCommand
cmdCommand.CommandType = adCmdText
Set recSet = cmdCommand.Execute
please help

 

[Robert Bruce]

I am using VBA in Excel to connect to a SQL server and retreve
data. That is working. The problem is that I want to run an Update
query to update one line in the database and it isn't working. Below
is an example of the code I am using.

Dim cmdCommand As New ADODB.Command
Dim recSet As New ADODB.Recordset
Dim cn As New ADODB.Connection
cmdCommand.ActiveConnection = cn
strSQLCommand = "UPDATE Materials.ReportName = test Where
materials.MaterialID = 5"
cmdCommand.CommandText = strSQLCommand
cmdCommand.CommandType = adCmdText
Set recSet = cmdCommand.Execute
please help

Your UPDATE syntax is wrong. The correct syntax for update is:

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

so try something like "UPDATE Materials set ReportName = 'test' Where
MaterialID = 5". Note that I have also enclosed the string value 'test' in
single quotes.

HTH

Rob

 

[RB Smissaert]

Try something like this:

Sub test()

Dim objCommand As ADODB.Command
Set objCommand = New ADODB.Command

With objCommand
.ActiveConnection = ADOConn
.CommandText = "UPDATE Materials " & _
"set ReportName = 'test' " & _
"Where MaterialID = 5"
.Execute
End With

End Sub


RBS