I have fully functional code that takes data in a range on an Excel sheet and ADDS NEW ROWS to a SQL database.
I am now faced with the need to UPDATE EXISTING ROWS instead of just adding new rows.
I think it will be best to DELETE ALL EXISTING ROWS matching the criteria then ADD NEW again.
My concern is, if I only update the rows listed in the table there could be an instance where there were 10 rows matching the criteria in the table but only 9 are required after the update. Better to delete 10 and add 9 than end up with a row of leftover old data; 9 new and 1 old. If I’m wrong, please advise.
My knowledge of SQL is minimal but I need to delete this:
Select * FROM TABLE_NAME Where DOC_ID = “123456”
(DOC_ID is a field linked to another table NOT a primary key/unique ID in this table.)
This is what I have preceding the ADD ROWS code where I think I should first DELETE ROWS
' Object type and CreateObject function are used instead of ADODB.Connection,
' ADODB.Command for late binding without reference to
' Microsoft ActiveX Data Objects 2.x Library
' ADO API Reference
' http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx
' Dim con As ADODB.Connection
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.ConnectionString = conString
con.Open
' Dim cmd As ADODB.Command
Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
' BeginTrans, CommitTrans, and RollbackTrans Methods (ADO)
' http://msdn.microsoft.com/en-us/library/ms680895(v=vs.85).aspx
Dim level As Long
level = con.BeginTrans
cmd.CommandType = 1 ' adCmdText
' Dim rst As ADODB.Recordset
Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")
With rst
Set .ActiveConnection = con
Bookmarks