开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜