[AccessD] Deleting Duplicates

Hi Max

Nice! Good example of the power of DAO.

/gustav


>>> max.wanadoo at gmail.com 16-05-2009 17:42 >>>
No worries Susan,
I would'nt have done that.  But as it happens I couldn't use Gustav's
posting because of the Not In predicate.  This is incredible slow on large
datasets.  I stopped it after an incredibly l.o.n.g. time when I ran out of
patience.
I then coded it as shown below and it finished before you can say jack
flash.
I just set up a temp table with only the fields that were, in combination,
set to a unique index and then ran the code

Max

Function removeupes()
  On Error GoTo errhandler
  Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset, sql As String
  sql = "Delete * from temp"
  CurrentDb.Execute (sql)
  Set rst1 = CurrentDb.OpenRecordset("Select * from AllRecords")
  Set rst2 = CurrentDb.OpenRecordset("Select * from Temp")
  rst1.MoveFirst
  Do While Not rst1.EOF
    rst2.AddNew
    rst2!ESTABLISHMENT = rst1!ESTABLISHMENT
    rst2!POSTCODE = rst1!POSTCODE
    rst2.update
    rst1.MoveNext
  Loop
Exithere
	' drop the temp table contents or delete it altogether. 
  sql = "Delete * from temp"
  CurrentDb.Execute (sql)  
  MsgBox "Done!"
  Set rst1 = Nothing: Set rst2 = Nothing
  Exit Function
errhandler:
  Select Case Err.Number
  Case 3022 'dupe
  rst1.Delete ' this is a dupe, so delete it.
  Resume Next
  Case Else
  MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
  End Select
  Resume exithere
End Function


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: 16 May 2009 14:53
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Deleting Duplicates

Oh my! ;)

That makes sense though, I just didn't think it through. I had pancakes for breakfast. :)

Susan H.

>
> It will delete the duplicates.  ALL of them!  And of course you generally 
> want to keep the FIRST
> record of a duplicate set.