none
Access/VBA code to delete duplicate records in MS Access table RRS feed

  • Question

  • Hello Developers,

    I have MS Access table with 20 fields and there are many records which are duplicates.

    I want access VBA code to delete duplicate records from this table. The code should check values in all 20 fields and if there are any duplicate record it should delete only duplicate record.

    Thanks for your time.

    Tuesday, December 6, 2011 4:02 PM

Answers

  • Zaveri:

    Without posting the code, I would approach it this way:

    (1) Create a query that lists all 20 fields.
    (2) Make sure each field in the query is sorted in ascending sequence
    (3) Use this query as the source of a recordset:

    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("qryMy20Fields") 

    (4) Save each of the 20 fields to variables
    (5) Read the next record
    (6) If the 20 fields are the same, delete the record
    (7) If they are not the same, then save the new 20 fields to the variables and keep looping

    If you want me to post a sample code for just three columns, let me know.  This logic is "historic" in the fact that it was used in the mainframe days to scan through records looking for control breaks.

    Regards, 


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Proposed as answer by Lawrence Ellefson Tuesday, December 6, 2011 4:42 PM
    • Marked as answer by Bruce Song Tuesday, December 20, 2011 3:06 AM
    Tuesday, December 6, 2011 4:19 PM

All replies

  • Zaveri:

    Without posting the code, I would approach it this way:

    (1) Create a query that lists all 20 fields.
    (2) Make sure each field in the query is sorted in ascending sequence
    (3) Use this query as the source of a recordset:

    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("qryMy20Fields") 

    (4) Save each of the 20 fields to variables
    (5) Read the next record
    (6) If the 20 fields are the same, delete the record
    (7) If they are not the same, then save the new 20 fields to the variables and keep looping

    If you want me to post a sample code for just three columns, let me know.  This logic is "historic" in the fact that it was used in the mainframe days to scan through records looking for control breaks.

    Regards, 


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Proposed as answer by Lawrence Ellefson Tuesday, December 6, 2011 4:42 PM
    • Marked as answer by Bruce Song Tuesday, December 20, 2011 3:06 AM
    Tuesday, December 6, 2011 4:19 PM
  • Zaveri:
    Here's some example code that deletes duplicate fields with a table with only 3 fields.  You will need to expand this to 20, and also declare variables to match your table specifications (String, Long, Integer, etc).  It is important that all fields in the query are sorted in ascending order.
    Since the "Post Code" feature is currently offline, here's the code just "Pasted" into the text window:
    Public Function EliminateDups()
    Dim db As DAO.Database
    Dim recIn As DAO.Recordset
    Dim strLastField1 As String
    Dim strLastField2 As String
    Dim strLastField3 As String
    Dim lngRecordsDeleted As Long
    lngRecordsDeleted = 0
    Set db = CurrentDb()
    Set recIn = db.OpenRecordset("qryMyRecords")
    If recIn.EOF Then
        MsgBox ("No Input Records")
        recIn.Close
        Set recIn = Nothing
        Set db = Nothing
        Exit Function
    End If
    Do
    If recIn!Field1 = strLastField1 And _
       recIn!Field2 = strLastField2 And _
       recIn!Field3 = strLastField3 Then
       recIn.Delete
       lngRecordsDeleted = lngRecordsDeleted + 1
    Else
        strLastField1 = recIn!Field1
        strLastField2 = recIn!Field2
        strLastField3 = recIn!Field3
    End If
    recIn.MoveNext
    Loop Until recIn.EOF
    recIn.Close
    Set recIn = Nothing
    Set db = Nothing
    MsgBox ("You Deleted " & lngRecordsDeleted & " Records")
    End Function
    Regards,

    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, December 6, 2011 4:42 PM
  • Thanks Rich for your quick resoponse. I will try to use your code and let you know if i face any problem.

    Thanks.

    Tuesday, December 6, 2011 4:53 PM
  • I don't htink the code will remove all the duplicates because when a row is deleted the next row will be skipped.  You need to start at the end of the recordset and move toward the front for the code to be reliable.
    jdweng
    Tuesday, December 6, 2011 7:04 PM
  • @jdweng:

    Jd:  I tested my routine extensively, and unlike Excel, it doesn't "slide records up" when you delete one.  Not only did it pass a test of 100 records of mixed duplicates and non-duplicates, if you step through the code in the debugger, you will see that after the record is deleted, you will get a "No Current Record" message in the Immediate window if you ask:  ? recIn!Field1.  So, until you issue a recIn.MoveNext, you are still on the deleted record.  I stand by the code :)

    jdweng:  Please try this yourself.

    From the Access Help File: In an updatable Recordset object, Delete removes the current record and makes it inaccessible. Although you can't edit or use the deleted record, it remains current. Once you move to another record, however, you can't make the deleted record current again. Subsequent references to a deleted record in a Recordset are invalid and produce an error.

    Best Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com




    • Edited by RichLocus Tuesday, December 6, 2011 7:58 PM
    Tuesday, December 6, 2011 7:29 PM