Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2015
    Posts
    31

    Answered: Deleting duplicates from Access Database

    Hello everyone,

    I'm currently having some technical difficulties with my access database. Basically, i have a hundreds of duplicates that i need to delete/remove however i have no skills at all in SQL. Therefore, i was hoping to be able to handle the situation by using a query. I've tried using logic to conquer the duplicates by adding criteria to my fields which lowers the amount of duplicates but doesn't get rid off them.

    Is there not a function that i can use enabling me to delete the older records and preserve the latest one ?

    I'm open to using SQL but as mentioned i have no experience at all

    Many thanks !

  2. Best Answer
    Posted by VLOOKUP

    "
    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("T1")
    
    sSQL = "SELECT DISTINCT Test, Test2, Test3 "
    sSQL = sSQL + "INTO tblMyTemp "
    sSQL = sSQL + "FROM T1 "
    
    '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
    Updated the VBA. This will create a new table without duplicates.

    It's not de dupping your original table, but it's what I use to remove duplicates if need. You could modify the VBA to effect the direct table.

    Always test on a backup!"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest https://support.office.com/en-us/art...1-42a3e49c63ae as a place to start.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's a link to Allen Browne's article on the subject:

    http://allenbrowne.com/subquery-01.html#DeDuplicate

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    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("T1")
    
    sSQL = "SELECT DISTINCT Test, Test2, Test3 "
    sSQL = sSQL + "INTO tblMyTemp "
    sSQL = sSQL + "FROM T1 "
    
    '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
    Updated the VBA. This will create a new table without duplicates.

    It's not de dupping your original table, but it's what I use to remove duplicates if need. You could modify the VBA to effect the direct table.

    Always test on a backup!
    Last edited by VLOOKUP; 05-06-15 at 11:44.

  6. #5
    Join Date
    Mar 2015
    Posts
    31
    Hello !
    Thank you for the quick responses ! I tried the first link and it didn't help much seeing as I am using Access in a French company and on a french version of Access the parameters weren't the same.

    Then moving on to VBA I have no idea how to use VBA however i will definitely try. Is there any way you could explain in a bit more detail how i should go about using this VBA ?

    Also, is it possible to delete duplicates from 2 tables simultaneously bearing in mind they both share the same key ? I guess so? After all the power of Access is supposedly unlimited !

    Thank you all very much !
    Last edited by Guy Winfield; 05-07-15 at 04:04.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Once Access has been launch pretty alt - f11. This should pull up the VBA IDE. From here you might need to Google at little bit, but you should be able to insert a module and paste the code in the editor and run it. Of course do this on a back up copy. Once you feel comfortable with the editor you can be a lot of fun. Procedural code is a lot of fun!

Posting Permissions

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