Hi Guys, I have tried numerous times, but the below code only updates the first row from the excel sheet in Access. I want it to update the whole sheet in existing table in Access.
I don't want to create new entries but only update existing entries. and all entries in the excel are from the Access itself.
I don't want to create new entries but only update existing entries. and all entries in the excel are from the Access itself.
Code:
Sub UpdateCompletedEntries()
data_source = Sheets("Emails").range("B1").Value
cnn.Open ("Provider = Microsoft.ACE.OLEDB.12.0; Data Source = '" & data_source & "'; JET OLEDB:Database Password=cadb@2015;")
Dim r As Long
i = 3
uid = Cells(i, 1).Value
Update = Cells(i, 6).Value
r = 3
Do While Len(range("A" & r).Formula) > 0
qry = "Update OPSC Set Live = " & Update & " Where Unique_ID = " & uid & ";"
rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
i = i + 1
r = r + 1
Loop
cnn.Close
End Sub