Excel VBA 更新 SQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2570174/
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
Excel VBA to Update SQL Table
提问by user307655
I have a small Excel program that is use to upload data to an SQL server.
我有一个用于将数据上传到 SQL 服务器的小型 Excel 程序。
This has been working well for a while.
这已经运行了一段时间。
My problem now is that I would like to offer to users a function to update an existing record in SQL.
我现在的问题是我想为用户提供一个功能来更新 SQL 中的现有记录。
As each row on this table has a unique id columne. There is a column call UID which is the primary key.
由于此表上的每一行都有一个唯一的 ID 列。有一个列调用 UID，它是主键。
This is part of the code currently to upload new data:
这是当前上传新数据的代码的一部分：
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & _
DatabaseName & ";Uid=" & UserID & ";Pwd=" & Password & ";"
rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic
For RowCounter = StartRow To EndRow
rs.AddNew
For ColCounter = 1 To NoOfFields
rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
Next ColCounter
Next RowCounter
rs.UpdateBatch
' Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
Is there anyway i can modify this code to update a particular UID rather than importing new records?
无论如何我可以修改此代码以更新特定的 UID 而不是导入新记录？
Thanks again for your help
再次感谢你的帮助
回答by Andomar
Instead of AddNewto open a new record, use Findto locate an existing one. Like:
不是AddNew打开新记录，而是Find用于定位现有记录。喜欢：
rs.Find "id = 100"
rs(1) = "ColumnOneValue"
Or:
或者：
rs.Find "LastName = 'Bush'"
rs("LastName") = "Blush"
It is possible to Findmultiple rows, and cycle over them using EOFand MoveNext.
可以Find多行，并使用EOF和循环遍历它们MoveNext。
