<

Go Premium for a chance to win a PS4. Enter to Win

x

Remove dupes in DB easily using VB6 and ADO

Published on
9,951 Points
3,951 Views
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] & "'"
   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
Comment
0 Comments

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority 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 process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month