<

Remove dupes in DB easily using VB6 and ADO

Published on
10,516 Points
4,516 Views
Last Modified:
Approved
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

Open in new window

0
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free