从Excel VBA更新SQL Server表

Question

我正在尝试使用以下代码来获取活动单元格并更新SQL Server中的表格。

Sub UpdateTable()
Dim rngName As Range
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=MyServer; " & _
            "Initial Catalog=Mydb;" & _
            "User ID=User;" & _
            "Password=Pwd;" & _
            "Trusted_Connection=No"
Set rngName = ActiveCell
'Debug.Print (rngName)
 Set cnn = New ADODB.Connection
Application.ScreenUpdating = False
cnn.Open cnnstr
Set rs = New ADODB.Recordset
uSQL = "UPDATE MyTable SET FieldNameX = 1 WHERE FieldNameY = '" & rngName & "' "
rs.CursorLocation = adUseClient
rs.Open uSQL, cnn, adOpenStatic, adLockOptimistic, adCmdText
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

当逐步执行代码时，rs.close行会出现运行时错误，显示“当对象关闭时不允许操作”。我已经在代码中设置并打开了记录集，为什么它会关闭呢？

我需要做什么来纠正这个问题，并让活动单元格填充查询并更新SQL Server中的表格？

- Jez

---

你能确认记录集 rs 是否成功打开吗？在即时窗口中尝试输入 ?rs.recordcount（使用 CTRL_G）。如果错误发生在 'rs.Open' 行，则连接可能未打开。 - Our Man in Bananas

我应该把rs.recordcount放在哪里？我把它放在了rs.open后面，但是运行时出现了“参数数量错误或无效属性赋值”的错误。 - Jez

你的更新SQL没有返回任何记录，因此你期望记录集为空。对于更新，最好使用“Connection.Execute”方法 - 有一个参数“RecordsAffected”，可以用来确定更新影响了多少行。http://msdn.microsoft.com/en-us/library/windows/desktop/ms675023(v=vs.85).aspx - Tim Williams

为什么你说我的更新SQL没有返回任何记录，当我在即时窗口中调试打印（Debug.Print）uSQL时，它会显示SQL语句。我已经在SSMS中测试过了，它可以更新记录。总是只有一个记录需要更新，那就是Excel中的活动单元格。我从SQL Server导入数据，更新查询将该记录标记为从表中下载的记录。 - Jez

你的更新没有返回任何记录：它只是更新表。这不会导致任何记录被选中并填充你的记录集。 - Tim Williams

我错过了那个，真尴尬！ - Our Man in Bananas

Answer 1

以下是我用来更新SQL Server表的代码，它可以按照我想要的方式工作。它获取活动单元格并进行更新。

Sub UpdateTable()
Dim cnn As ADODB.Connection
Dim uSQL As String
Dim rngName As Range
Set cnn = New Connection
cnnstr = "Provider=SQLOLEDB; " & _
            "Data Source=MyServer; " & _
            "Initial Catalog=Mydb;" & _
            "User ID=User;" & _
            "Password=Pwd;" & _
            "Trusted_Connection=No"
    Set rngName = ActiveCell
cnn.Open cnnstr
uSQL = "UPDATE MyTable SET FieldNameX = 1 WHERE FieldNameY= '" & rngName & "' "
'Debug.Print (uSQL)
cnn.Execute uSQL
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

- Jez