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

    delete duplicate records (2000 sr 1)

    After this macro (from Excel) is posisble to find a duplicate record into table and delete entire line it?
    For exeample find in the field CRO and delete entire record in the table...

    Sub ADO_CDI_50()
    ' exports data from the active worksheet to a table in an Access database
    ' this procedure must be edited before use
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Dim rsFind As ADODB.Recordset

    ' connect to the Access database
    Set cn = New ADODB.Connection

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=GCD01F4500DATIPUBBLICABOUASSPROVA.MDB;"
    ' "Data Source=D:PROVAPROVA.MDB;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "CDI_50", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 7 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
    If Not AlreadyExists(rs, "SERVIZIO", Range("S" & r).Text) Then
    rs.AddNew ' create a new record
    'End If
    With rs
    'add values to each field in the record
    Sheets("L0785_CDI_50").Select
    .Fields("DATA_CONT") = Range("A" & r).Value
    .Fields("DIP") = Range("B" & r).Value
    .Fields("COD_BATCH") = Range("C" & r).Value
    .Fields("C_C") = Range("D" & r).Value
    .Fields("NOMINATIVO") = Range("E" & r).Value
    .Fields("CAUS") = Range("F" & r).Value
    .Fields("DARE") = Range("G" & r).Value
    .Fields("AVERE") = Range("H" & r).Value
    .Fields("VAL") = Range("I" & r).Value
    .Fields("SPORT_MIT") = Range("J" & r).Value
    .Fields("ANOM") = Range("K" & r).Value
    .Fields("DESCR") = Range("L" & r).Value
    .Fields("CRO") = Range("M" & r).Value
    .Fields("ABI") = Range("N" & r).Value
    .Fields("CAB") = Range("O" & r).Value
    .Fields("PAG_IMP") = Range("P" & r).Value
    .Fields("NR_ASS") = Range("Q" & r).Value
    .Fields("MT") = Range("R" & r).Value
    .Fields("SERVIZIO") = Range("S" & r).Value
    .Fields("NOTE_BOU") = Range("T" & r).Value
    .Fields("SPESE") = Range("U" & r).Value
    .Fields("DATA_ATT") = Range("V" & r).Value
    .Fields("COD") = Range("W" & r).Value
    .Update ' stores the new record
    End With
    End If
    r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete duplicate records (2000 sr 1)

    Once you run the Excel VBA code, all the data should now reside in an Access table. Try gouping on the CRO field and counting on another field (an index, primary key or CRO itself) in a query. If the CountOfFieldName comes up greater than 1, you know there are duplicates and you can then determine how you want to delete them.
    I hope that help.
    Dashiell

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

    Re: delete duplicate records (2000 sr 1)

    You asked almost the same question in <post#=425198>post 425198</post#>. The reply in that thread can be applied to this one too, so to avoid further duplication, I will lock this thread.

Posting Permissions

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