2

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

Improve this question

1 Answer 1

Reset to default
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.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.