Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Remove duplicate records in Access with VBA

This is a discussion on Remove duplicate records in Access with VBA within the Microsoft Access forums, part of the Question Forums category; I am trying to delete the duplicate records in an Access database. In the code below, all the records, including ...

  1. #1
    Board Regular
    Join Date
    Jun 2005
    Location
    North Queensland
    Posts
    824

    Default Remove duplicate records in Access with VBA

    I am trying to delete the duplicate records in an Access database.
    In the code below, all the records, including duplicates, are inserted in the new tblmyTemp table.

    Can anyone please advise where I am going wrong, as I thought DISTINCTROW was intended to remove duplicates.

    Any suggestions on how I can get a table without duplicate records?

    All help and suggestions very gratefully received.

    Code:
    Sub Del_Dupes()
    
    ' delete duplicate records in a database table
    
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim sSQL$
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblTransformer")
    
    sSQL = "SELECT DISTINCTROW INTO tblMyTemp FROM tblTransformer"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    
    MsgBox "A table WITHOUT DUPLICATES has been created as tblMyTemp", _
      vbOKOnly, "Created"
    
    Set rst = Nothing
    Set db = Nothing
    
    End Sub
    My regards,

    Joe

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    North Queensland
    Posts
    824

    Default GOT IT!!

    After lots of reading and fiddling around, discovered that DISTINCTROW will not work with a single table. So I used DISTINCT *

    YESSSSS!!!!!

    Correct code shown below

    Code:
    Sub Del_Dupes()
    
    ' delete duplicate records in a database table
    
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim sSQL$
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblTransformer")
    
    sSQL = "SELECT DISTINCT *"
    sSQL = sSQL + "INTO tblMyTemp "
    sSQL = sSQL + "FROM tblTransformer"
    
    Stop
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    
    MsgBox "A table WITHOUT DUPLICATES has been created as tblMyTemp", _
      vbOKOnly, "Created"
    
    Set rst = Nothing
    Set db = Nothing
    
    End Sub
    My regards,

    Joe

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    86

    Default Re: Remove duplicate records in Access with VBA

    Is that a valid way to eliminate duplicates???

    If I have a table with 40 columns, will that piece of code work?

    Thanks

  4. #4
    Board Regular
    Join Date
    Mar 2011
    Posts
    86

    Default Re: Remove duplicate records in Access with VBA

    What is the best way to delete duplicates?

    I have code in VBA that bugs for large tables.

    I have 1.3 million lines and about 50 columns, huge table.

    Any idea?

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    86

    Default Re: Remove duplicate records in Access with VBA

    I've been using this code so far:

    Sub DeleteDuplicateRecords()
    ' Deletes exact duplicates from the specified table.
    ' No user confirmation is required. Use with caution.
    StrTableName = InputBox("Enter tablename")


    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 "
    ' Build a sort string to make sure duplicate records are
    ' adjacent. Can't sort on OLE or Memo fields,though.
    For Each fld In tdf.Fields
    If (fld.Type <> dbMemo) And _
    (fld.Type <> dbLongBinary) Then
    strSQL = strSQL & "[" & fld.Name & "], "
    End If
    Next fld
    ' Remove the extra comma and space from the SQL
    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

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default Re: Remove duplicate records in Access with VBA

    all 50 fields are required to uniquely identify a record? Aren't then any keys in this table?

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Mar 2011
    Posts
    86

    Default Re: Remove duplicate records in Access with VBA

    Maybe not all 50 are needed but I would still want to understand why I can't use the code above.

    It seems like it's a size problem... I can a warning message saying "either the size is greater than 2GB or"

  8. #8
    Board Regular
    Join Date
    Mar 2011
    Posts
    86

    Default Re: Remove duplicate records in Access with VBA

    I don't really have keys.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default Re: Remove duplicate records in Access with VBA

    I probably would not want to use a recordset loop with such a large table. But it is hard to suggest alternatives when it takes 50 fields to identify a record. Having memo fields adds to the difficulty. The code you are doing is not a typically way to delete dupes, in any case.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,203

    Default Re: Remove duplicate records in Access with VBA

    Cheap and nasty method;

    Build a query. Add every field to the grid EXCEPT the memos -- they shouldn't be used for searching / grouping.
    Turn the query into a Totals query by pressing the big Sigma button.
    Hold your breath, then run the query. With large datasets and no indexing it could take a while.

    Beforehand, try running a Compact & Repair to reduce the szie of the database. It could help with the speed and make you less likely to hit the 2GB limit.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com