Remove dupes in DB easily using VB6 and ADO

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate rows fast, with no overhead. The best thing about this method is that it works on tables without identity keys, which is always a problem if they don't exist. It also works for SQL Server and Access.

My example assumes the DB is already open, named "verDB", and rs is declared as an ADO RecordSet. In my example, I have a table called "Tunl_CustHist". What I am looking for is any entry where the field "Mach" and "Datim" are collectively the same. I am not looking for entire duplicated rows, just those in which two fields match. For example, two entries with Mach set to 100 and Datim set to the same date/time. You can expand upon this example to include all fields if complete dupe deletion is needed (every field is the same). You will, of course, change the table and field names in the example to your names.

Note that upon finding dupes, I delete ALL rows. In my company's environment, dupes on the two fields used indicate an error, and both need deleting. You can change this by using the supplied COUNT variable, and leave one record per duplication. Or, before deleting, you can check the other fields to see if they equal a desired value. If so, don't delete. If they don't meet your criteria, delete the row.

Here is the code:

Rem *** Remove duplicate entries ***
                      SQL$ = "SELECT [Mach], [Datim], COUNT(*) - 1 From [Tunl_CustHist] GROUP BY [Mach], [Datim] HAVING COUNT(*) > 1"
                      rs.Open SQL$, verDB, adOpenForwardOnly, adLockReadOnly
                      While Not rs.EOF
                         If booSQLServer Then   'global variable telling me which type of database I'm dealing with
                            SQL$ = "DELETE FROM [Tunl_CustHist] WHERE [Mach] = " & CInt(rs![Mach]) & " AND [Datim] = '" & rs![datim] & "'"
                         Else
                            SQL$ = "DELETE * FROM [Tunl_CustHist] WHERE [Mach] = " & CInt(rs![Mach]) & " AND [Datim] = #" & rs![datim] & "#"
                         End If
                         verDB.Execute SQL$, , adExecuteNoRecords
                         rs.MoveNext
                      Wend
                      rs.Close

Select all Open in new window