Excel VBA to Update SQL Table

Question

Hi All,

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 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

Answer 1

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.