<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Remove dupes in DB easily using VB6 and ADO

Published on
9,891 Points
3,891 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Join & Write a Comment

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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month