Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    remove dupes in fields via VBA (6.0)

    Have:
    mymdb.mdb
    mytable
    myfiledname

    how to remove from field myfiledname all duplicates records?

    Note: i want to use the code in VBA for excel.
    Tks.

    Hans in my big Archive found this old code, wath you think...:

    Option Explicit
    Global CN1 As ADODB.Connection, RS1 As ADODB.Recordset
    Global Const gPROVADatabasePath2 = "Data Source=CB2.MDB;"
    Sub test_dupes()
    Dim DUPLICATO
    Set CN1 = New ADODB.Connection
    CN1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    Set RS1 = New ADODB.Recordset
    RS1.Open "COPE", CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    RS1.movefirst

    Do Until RS1.EOF
    If DUPLICATO = RS1!COPE Then
    RS1.Delete
    Else
    DUPLICATO = RS1!COPE
    End If
    RS1.movenext
    Loop

    RS1.Close
    CN1.Close
    Set RS1 = Nothing
    Set CN1 = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: remove dupes in fields via VBA (6.0)

    Instead of deleting duplicates, you should prevent them. If you set a unique key on myfieldname, it is impossible to enter duplicate values in this field.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remove dupes in fields via VBA (6.0)

    Peraphs found a solution in my "Big" archive...
    Tell me if is a good idea
    Tks in other case.
    Option Explicit
    Global CN1 As ADODB.Connection, RS1 As ADODB.Recordset
    Global Const gPROVADatabasePath2 = "Data Source=CB2.MDB;"
    Sub test_dupes()

    Dim DUPLICATO
    Set CN1 = New ADODB.Connection
    CN1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    Set RS1 = New ADODB.Recordset
    RS1.Open "COPE", CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    RS1.movefirst

    Do Until RS1.EOF
    If DUPLICATO = RS1!COPE Then
    RS1.Delete
    Else
    DUPLICATO = RS1!COPE
    End If
    RS1.movenext
    Loop

    RS1.Close
    CN1.Close
    Set RS1 = Nothing
    Set CN1 = Nothing

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: remove dupes in fields via VBA (6.0)

    This code will only work if the recordset is sorted on the COPE field. If the data are not sorted on COPE, some duplicates will not be removed.

    Once again, prevention is the best cure - you should try to avoid duplicate values from the start, instead of removing them afterwards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •