Update or Ignore if field data already exist

[dan_pafc]

Hi - Here is an extract of by code to insert new data into a table (BOData). Because the source data has the potential to contain the same data multiple times, I would like to amend my code so that if 'DataDate' and 'Avaya' already exist in the table - it updates (or ignores). Ive seen a few other examples which dont really fit my way of coding. Thanks!!

sSQL = "INSERT INTO BOData (DataDate, Avaya, Staffed, SiebelName)" & _
        "VALUES ('" & ActiveSheet.Range("A3") & "', '" & ActiveCell.Offset(0, -1).Value & "', '" & ActiveCell.Offset(0, 1).Value & "', '" & SN & "');"

'Edit the data using ADO
Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect, _
            adOpenForwardOnly, adLockOptimistic, adCmdText
Set rsData = Nothing

 

[ranman256]

this can be done without any code.

1. use a make table (or append table) query to bring in all the data
2. join this new table with the main data table, run update query to mark the existing records in the new table
3. delete marked records in new table
4. append new recs to the main data table (free of duplicates)

1 macro, 4 queries, no code.