Excel VBA ADO更新SQL表/记录

Question

通过使用此SQL字符串，我成功地更新了SQL表并进行了记录。

"UPDATE Breach_Test_Key SET [VAL_BREACH_REASON] = 'SOME BREACH REASON'  WHERE  [ID] = 1"

有两件事我正在努力做到，那就是：

1. 更新SQL表中的两个特定列，如何在集合中定义两个列？
2. 我还需要将Excel中表中的所有记录更新回SQL表( SQL表中都将存在这些记录)。

ID字段将始终匹配，因为数据来自此表。

请有人引导我通过SQL结构好吗？

谢谢@MatteoNNZ对第1部分的帮助，这是我现在用于更新多列的代码

uSQL = "UPDATE Breach_Test_Key SET [VAL_BREACH_REASON] = 'SOME BREACH REASON1',[VAL_BREACH_DETAIL] = 'SOME BREACH DETAIL1'  WHERE  [ID] = 1"

因此，对于第二部分，我实际上没有足够的发布内容，但是如果我在字符串中指定了一个值，我希望循环遍历excel表/列是动态的。有什么指示吗？

Answer 1

好的，我得到了我想要的结果。正在按预期使用vba中的循环选项更新记录。这也许不是我想要达到的最漂亮的方式，但它是有效的。

如果有人想提出改进建议的话，我会全神贯注地倾听。但这是我的最后一个宏：

Private Sub UpdateTable()

Dim cnn As ADODB.Connection
Dim uSQL As String
Dim strWard As String
Dim strDate As Date
Dim strUser As String


'=====USER NAME VBA================
    Set WSHnet = CreateObject("WScript.Network")
    UserName = WSHnet.UserName
    UserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & UserDomain & "/" & UserName & ",user")
    UserFullName = objUser.FullName
'===================================


'strDate is a stamp of the date and time when the update button was pressed
strDate = Format(Now(), "dd/mm/yyyy hh:mm:ss")
'User is the username of the person logged into the PC the report is updated from
strUser = UserFullName


'Set Connection string
Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=ServerName; " & _
            "Initial Catalog=dbName;" & _
           **USER DETAILS HERE**

'Connect and Run the SQL statement to update the reords.
cnn.Open cnnstr
Dim row As Range
For Each row In [tbl_data].Rows
    uSQL = "UPDATE Breach_Test_Key SET [VAL_BREACH_REASON] = '" & (row.Columns(row.ListObject.ListColumns("VAL_BREACH_REASON").Index).Value) & _
    "' ,[VAL_BREACH_DETAIL] = ' " & (row.Columns(row.ListObject.ListColumns("VAL_BREACH_DETAIL").Index).Value) & _ 
    "' WHERE  [ID] = '" & (row.Columns(row.ListObject.ListColumns("ID").Index).Value) & "'"

    'Debug.Print uSQL
cnn.Execute uSQL
Next

cnn.Close
Set cnn = Nothing
Exit Sub
End Sub