Update Access database

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
102
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,666
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,561
Messages
5,487,563
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top