Excel VBA to Update SQL Table
I have a small Excel program that is use to upload data to an SQL server.
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.
As each row on this table has a unique id columne. There is a column call UID which is the primary key.
This is part of the code currently to upload new data:
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL开发者_StackOverflow社区 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?
Thanks again for your help
Instead of AddNew
to open a new record, use Find
to locate an existing one. Like:
rs.Find "id = 100"
rs(1) = "ColumnOneValue"
Or:
rs.Find "LastName = 'Bush'"
rs("LastName") = "Blush"
It is possible to Find
multiple rows, and cycle over them using EOF
and MoveNext
.
精彩评论