Updating Table in Access from Excel using DAO

By progof ·
Hi there, I am currently exporting data from a set of defined columns in an Excel spreadsheet into an Access table using DAO. It works fine, but the problem is that for a specific month e.g. April I upload it once, but when there is a change and I upload it again, I need to delete the old data (without affecting the data from other previous months). This is the code that I have at the moment:

Dim db As Database, rs As Recordset, ws As Worksheet, r As Long

Set db = OpenDatabase("C:\BS Sales.mdb")
Set rs = db.OpenRecordset("CMZ_1_PL_EXPENSE", dbOpenTable)
Set ws = ThisWorkbook.Worksheets("Output")
r = 2
Do While Len(ws.Range("A" & r).Formula) > 0
With rs

.Fields("Month") = ws.Range("A" & r).Value
.Fields("area") = ws.Range("B" & r).Value
.Fields("code") = ws.Range("C" & r).Value
End With
r = r + 1 ' next row
Set rs = Nothing
Set db = Nothing

I would like to add a condition that will allow to delete from the table the existing records whose "Date" is the same as the ones I am trying to add.

Many thanks for your time and help

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by Maevinn In reply to Updating Table in Access ...

You'll probably want to make it a separate function/object. What you'll need to do is create a join between the existing records in the table and the spreadsheet, and then use the update function.

Personally, I'd probably link the excel spreadsheet in as a table and run it all through a query, but I'm lazy.

Related Discussions

Related Forums