Excel VBA to Update SQL Table

Question

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

Andomar · Accepted Answer · 2010-04-03 08:58:24Z

3

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.

answered Apr 3, 2010 at 8:58

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA to Update SQL Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related