Excel VBA ADO UPDATE SQL表/记录 [英] Excel VBA ADO UPDATE SQL Table/Record

问题描述

我已成功使用此SQL字符串更新了SQL表并记录了

I have managed to update an SQL table and record by using this SQL string

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

我想要实现的两件事是：

Two things I am trying to achieve and that is:

1. 更新SQL表中的两个特定列，如何在SET中定义两个
   列？


2. 我还需要将Excel中的表中的所有记录更新回
   到SQL表中（这些都将存在于SQL表中）。

ID字段将始终匹配此表中的数据。

The ID field will always match as the data is from this table.

请有人指导我

感谢@MatteoNNZ提供第1部分的帮助，这是我现在用来更新多列的代码

Thank you @MatteoNNZ for help with part 1, this is the code I am now using to update multiple columns

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

因此对于第二部分，我实际上没有足够的信息来发布但是，如果我在字符串中指定了一个值，我希望它可以动态遍历excel表/列。

So for part two, I haven't actually got anything sufficient to post but where I have specified a value in the string I would rather it be dynamic to loop through the excel table/column. Any pointers?

推荐答案

好，所以我得到了想要的结果。
将使用vba中的循环选项按预期更新记录。
这可能不是我尝试实现的最漂亮的方法，但是它可以起作用。

Ok so I got the results I wanted. The records are being updated as expected using a loop option in the vba. It may not be the prettiest way of doing what I was trying to achieve but it works.

如果有人想提出改进建议，那么我无所不能。
但是这是我的最终宏：

If anyone would like to suggest improvements then I am all ears. But here is my final macro:

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

这篇关于Excel VBA ADO UPDATE SQL表/记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！