How to delete duplicate records in a table

[chundusmgs]

HOW TO DELETE DUPLICATE RECORDS
create a query with all the fields and an additional exp field combining all fields like=[field1] &[fild2] &[field3] . Run this qry as a make table query. Copy this table to another table with structure only.Fix the expression field as unique key and append to this table all records from the table which was created through make table query.

 

[HiTechCoach]

Are you really wanting to do this?

 

[smig]

something is wrong in your table design if you have duplicate records.

 

[vbaInet]

This (apparently) is "advice" lol

 

[smig]

sure it's advice, but my guess he had to do it because he had duplicate records

 

[vbaInet]

I was being a little bit sarcastic there smig. That was why I put advice in quotes lol.

 

[smig]

LOL
I thought you where talking to me

 

[brixie11]

I use that code for deleting duplicates:

Sub DeleteDuplicateRecords(strTableName As String)
[COLOR=green]   ' Deletes exact duplicates from the specified table.[/COLOR]
[COLOR=green]   ' No user confirmation is required. Use with caution.[/COLOR]
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    Dim varBookmark As Variant
 
    Set tdf = DBEngine(0)(0).TableDefs(strTableName)
    strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
[COLOR=green]   ' Build a sort string to make sure duplicate records are[/COLOR]
[COLOR=green]   ' adjacent. Can't sort on OLE or Memo fields,though.[/COLOR]
    For Each fld In tdf.Fields
        If (fld.Type <>  dbMemo) And _
         (fld.Type
         <>  dbLongBinary) Then
            strSQL = strSQL & fld.Name & ", "
        End If
    Next fld
[COLOR=green]   ' Remove the extra comma and space from the SQL[/COLOR]
    strSQL = Left(strSQL, Len(strSQL) - 2)
    Set tdf = Nothing
 
    Set rst = CurrentDb.OpenRecordset(strSQL)
    Set rst2 = rst.Clone
    rst.MoveNext
    Do Until rst.EOF
        varBookmark = rst.Bookmark
        For Each fld In rst.Fields
            If fld.Value <>  rst2.Fields(fld.Name).Value Then
                GoTo NextRecord
            End If
        Next fld
        rst.Delete
        GoTo SkipBookmark
NextRecord:
        rst2.Bookmark = varBookmark
SkipBookmark:
        rst.MoveNext
    Loop
End Sub

 

[Rich]

Good job this never made it to the "samples" forum