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 ...
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.