从 Excel VBA 更新 SQL Server 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22229765/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update SQL Server table from Excel VBA
提问by Jez
I'm trying to use the below code to take the active cell and update a table in SQL Server.
我正在尝试使用以下代码获取活动单元格并更新 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
When stepping through the code, it run time errors on the line rs.close, says Operation is not allowed when the object is closedI've set and opened the record set in the code so why would it be closed?
当单步执行代码时，它在 rs.close 行上运行时错误，说Operation is not allowed when the object is closed我已经在代码中设置并打开了记录集，为什么要关闭它？
What would I need to do to correct the issue and let the active cell populate the query and update the table in SQL Server?
我需要做什么来纠正问题并让活动单元格填充查询并更新 SQL Server 中的表？
回答by Jez
This below is the code I used to be able to update the table in SQL Server, this works just how I wanted. It takes the activecell and updates.
下面是我曾经能够在 SQL Server 中更新表的代码，这正是我想要的。它需要 activecell 和更新。
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
