Forum Discussion

Tod_Lay's avatar
Tod_Lay
Copper Contributor
Aug 05, 2025

Better way to check database for dups

I have vba in Excel that incorporates SQL to iterate through all the records in a sheet and checks the databsase for that record using a combination of two fields to make a unique id. If it's in the database then it's deleted from the sheet. Otherwise it goes to the next one. Easy enough. I've had this code for years and it's always been fine but now there are too many records to do it this way. It takes too long. 

What would be a better way to query the SQL database to return those records that are dups and then delete them from the sheet. 

Here's an idea of the snippet of code I currently use:

'i is the record number from bottom to top

For i = DataSheet.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        CountyName = DataSheet.Cells(i, 1).Value
        CaseNumber = DataSheet.Cells(i, 3).Value

        strSQL = "SELECT County, Case_Number FROM tblAdLetter " & _
                 "WHERE County = '" & CountyName & "' AND Case_Number = '" & CaseNumber & "'"

        rs.Open strSQL, cn

        If Not rs.EOF Then
            DataSheet.Rows(i).Delete
        End If

        rs.Close
    Next i

Is there a better way or faster way?

 

tod

 

 

 

1 Reply

  • Arya1978's avatar
    Arya1978
    Copper Contributor
    Mar 23, 2026

    Here’s a sketch of how you might restructure:

    Dim rs As ADODB.Recordset

    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")

     

    ' Build a temp table or IN clause query

    strSQL = "SELECT County, Case_Number FROM tblAdLetter WHERE (County, Case_Number) IN (...)"

     

    rs.Open strSQL, cn

     

    Do Until rs.EOF

    dict(rs!County & "|" & rs!Case_Number) = True

    rs.MoveNext

    Loop

    rs.Close

     

    ' Now loop Excel rows once

    For i = DataSheet.Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1

    key = DataSheet.Cells(i, 1).Value & "|" & DataSheet.Cells(i, 3).Value

    If dict.Exists(key) Then

    DataSheet.Rows(i).Delete

    End If

    Next i

    Why This Is Faster

    • Only one query to SQL instead of thousands.
    • Dictionary lookups in VBA are very fast.
    • One pass through the sheet instead of repeated DB calls.

    If your dataset is very large, the temporary table + JOIN method is the most scalable.