Forum Discussion

Tod_Lay's avatar
Tod_Lay
Copper Contributor
Aug 06, 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

 

 

 

No RepliesBe the first to reply

Resources