Remove dupes in DB easily using VB6 and ADO

Published on
10,169 Points
Last Modified:
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] & "'"
      SQL$ = "DELETE * FROM [Tunl_CustHist] WHERE [Mach] = " & CInt(rs![Mach]) & " AND [Datim] = #" & rs![datim] & "#"
   End If
   verDB.Execute SQL$, , adExecuteNoRecords

Open in new window


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Join & Write a Comment

As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month