Update Access database

[johnsonk]

Hi, I am using the below code to transfer data from an excel sheet what changes on a daily bases but when I transfer the data it just keeps adding instead of updating so I need the code to update existing and add new if any. I know very little about Access so any help would be great. Not sure is this should be in the Access or Excel forum.

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
"Data Source=\\Admin\MasterDB.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "PriceSheet", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("LABEL CODE") = Range("A" & r).Value
.Fields("PRODUCT CODE") = Range("B" & r).Value
.Fields("PRODUCT DESCRIPTION") = Range("C" & r).Value
.Fields("FIXED / CATCH WEIGHT") = Range("D" & r).Value
.Fields("PACK PRICE") = Range("E" & r).Value
.Fields("PRICE PER KG") = Range("F" & r).Value
.Fields("BARCODE") = Range("G" & r).Value
.Fields("USE BY") = Range("H" & r).Value
.Fields("DISPLAY UNTIL") = Range("I" & r).Value
.Fields("EXTENDED MAX LIFE") = Range("J" & r).Value
.Fields("PACK TARE") = Range("K" & r).Value
.Fields("PER CASE") = Range("L" & r).Value
.Fields("CASE SIZE") = Range("M" & r).Value
.Fields("PROMO DESCRIPTION") = Range("N" & r).Value
.Fields("PROMO CODE") = Range("O" & r).Value
.Fields("ING CODE") = Range("P" & r).Value
.Fields("ING DESCRIPTION") = Range("Q" & r).Value
.Fields("ING QTY") = Range("R" & r).Value
.Fields("GROUP") = Range("S" & r).Value
.Fields("VERSION") = Range("T" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Regards

 

[Joe4]

Your best bet is probably to import the data into a Temporary Table.

Then, create two Action Queries in Access.
1. An Update Query, that looks for matches between your Temporary Table and Permanent Table, and updates the fields of the matched record.
2. An Append Query, that adds the unmatched records between your Temporary Table and Permanent Table to the Permanent Table.

See here for details: MS Access - Action Queries - Tutorialspoint
You can find a lot more explanations and tutorials with Google searches.