Forum Discussion
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