Avoid duplicates before they are stored in Access

Question

I have an Excel file that stores freshly produced data into Access (before save event), then cleans the Excel. What I want to do is to find out if the macro is going to store a duplicate based on column 4, named 'Vendor_Parts' and skip the Access export for that item (which already exists in Access), then continue exporting. I have the following code running up smoothly:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim erow As Integer

' open database
Set DB = DAO.OpenDatabase("my companies' database path")

' open table as a recordset
Set RS = DB.OpenRecordset("WDATA")

'set last empty row
erow = Sheets("Product Hierarchy").Cells(Rows.Count, 4).End(xlUp).Row

'iterate through each cell and fill values in Access

      With RS
      For erow = 2 To erow
            .AddNew
            !Date = Cells(erow, 1)
            !SKU_Rep = Cells(erow, 2)
            !Vendor_Parts = Cells(erow, 4)
            !PH = Cells(erow, 5)
            !GM = Cells(erow, 6)
            ' add more fields here
          .Update
       Next erow

    End With

'cleanup database
RS.Close

' close the database to avoid lockdown
DB.Close

Set RS = Nothing
Set DB = Nothing

'Clean information already sent to access
Range("A2:F1000000").ClearContents


End Sub

Any ideas for calling a duplicate checker that avoids export? Thank you

score 0 · Answer 1 · answered Feb 14 '18 at 21:14

You can use a query to check for duplicates, and import all non-duplicate data.

This answer outlines a general way to construct a query to import Excel data into Access. We can add a NOT EXISTS clause and check if a duplicate already exists

strSQL = "INSERT INTO WDATA([Date], SKU_Rep, Vendor_Parts, [other], [fields]) " & _
"SELECT F1, F2, F3, etc " & _
"FROM [Excel 12.0 Macro; HDR=No;Database=" & ActiveWorkbook.FullName & "].[Product Hierarchy$A2:G" & Sheets("Product Hierarchy").Cells(Rows.Count, 4).End(xlUp).Row & "]" & _
"WHERE NOT EXISTS (SELECT 1 FROM WDATA WHERE [Date] = F1 AND SKU_Rep = F2 And etc)"
db.Execute strSQL
db.Close

In the end I resorted to querying the database first in Excel and using formulas to determine whether the item should be saved. It works. Your solution gave me the idea to get there. — Ali Z, Feb 27 '18 at 15:55

Avoid duplicates before they are stored in Access

1 Answers1