Find and delete duplicate records from a table

[AccessHunter]

I am using the following code to find and delete records in a table. The logic will go through each record and if a duplicate row is found will delete it. I ran this code and it worked the first time. Its not deleting the rows when I tried the second time. I debugged the code and its actually going through the delete step but the row is not getting deleted as it did the first time. Please help.

Thanks in advance.

Function DeleteDuplicates_Click()
On Error Resume Next

 Dim db As Database, rst As Recordset
 Dim strDupName As String, strSaveName As String
 Dim strSQL As String
 strSQL = "SELECT [JOBNUM], [trnd] FROM Tableqryduplicates"
 
 Set db = CurrentDb()
 Set rst = db.openrecordset(strSQL, dbOpenSnapshot)
  
 
 If rst.BOF And rst.EOF Then
   MsgBox "No records to process"
 Else
   rst.MoveFirst
   Do Until rst.EOF
     strDupName = rst.Fields("JOBNUM") & rst.Fields("trnd")
     
     If strDupName = strSaveName Then
       rst.Delete
     Else
       strSaveName = rst.Fields("JOBNUM") & rst.Fields("trnd")
     End If
     rst.MoveNext
   Loop
   
   rst.Close
   Set rst = Nothing
   Set db = Nothing
 End If
     
 
End Function

[Megalog]

Try adding a rst.edit or rst.delete before any changes, and an rst.update afterwards (before the movenext/loop)? I do pretty much the same thing, except with adding rows, and it needs an addnew/update to stick. I'm assuming it's the same with delete.

And maybe specify your recordset as DAO like:
"Dim db As Database, rst As DAO.Recordset"

[NeoPa]

Nice ideas.
I checked out the .Edit & .AddNew and neither is required for a .Delete.
Maybe the DAO.Recordset will help. Explicitly dimming it as DAO would force it to use the methods we are expecting.

AccessHunter, can you let us know if any of this helps at all?

[AccessHunter]

I used dbOpenDynaset instead of dbOpenSnapshot and it worked.

To me it looked like when I ran this the first time successfully with dbOpenSnapshot, it might have taken a snapshot of the resultant data (after the rows got deleted) and then when I tried 2nd time (with dbOpenSnapshot again) it was looking at that same data !!..not sure about that, its just my assumption. Correct me if I am wrong.

Thanks

[NeoPa]

I can't explain why it appears to have worked before. I wouldn't expect it to allow any amendments to a snapshot query. Only a dynamic query should work with deletions as far as I understand.

[AccessHunter]

Here is my new dilemma, please help me as I am new to VB.
I look at [job nbr], [seq nbr] and [trnd] to detemine if 2 rows are duplicates as per the code below. But I only want to delete a row based on the following logic.

If strDupName = strSaveName Then,

If ptyp(associated with strSaveName) or ptyp(associated with
strDupName) is not Null
If ptyp(associated with strSaveName) is Null,
delete that row,
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") &
rst.Fields("trn d")
Get next row.
Else, if ptyp(associated with strDupName) is Null,
delete that row
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") &
rst.Fields("trn d")
Get next row
End If
End If

If [p-lnam]+[p-fnam](associated with strSaveName) or [p-lnam]+[p-fnam]
(associated with strDupName) is not Null
If [p-lnam]+[p-fnam](associated with strSaveName) is Null
delete that row
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") &
rst.Fields("trn d")
Get next row.
Else, if [p-lnam]+[p-fnam] (associated with strDupName) is Null,
delete that row
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") &
rst.Fields("trn d")
Get next row
End If
End If


If [agency-name](associated with strSaveName) or [agency-name](associated
with strDupName) is not Null
If [agency-name](associated with strSaveName) is Null
delete that row
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") &
rst.Fields("trn d")
Get next row.
Else, if [agency-name](associated with strDupName) is Null,
delete that row
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") &
rst.Fields("trn d")
Get next row
End If
End If

-------------------------------------------------------------------------------------------------------------------

This is the code that works now,


Function DeleteDuplicate s_Click()
On Error Resume Next

Dim db As Database, rst As Recordset
Dim strDupName As String, strSaveName As String
Dim strSQL As String
strSQL = "SELECT [job nbr], [seq nbr], [trnd], [ptyp], [p-lnam], [p-fnam], [agency-name]FROM Tableqryduplica tes"

Set db = CurrentDb()
Set rst = db.openrecordse t(strSQL, dbOpenDynaset)

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF

strDupName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") & rst.Fields("trn d")

If strDupName = strSaveName Then
(This is where I want the logic that I just explained above)
rst.Delete
Else
strSaveName = rst.Fields("cas e nbr") & rst.Fields("seq nbr") & rst.Fields("trn d")

End If

rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
End If

End Function
-----------------------------------------------------------------------------------------------------------------

Thanks a lot.

[Relui]

I use this (test.zip unpack and check module). Of course is only a model. From this you can go and develop your solution.
I don't say is the best, a very fancy or the greatest cool solution ... but it works.
I didn't put any comments about author rights but if you mention my name in your solution I realy don't mind ...

[NeoPa]

Hi.

Just so you know, AccessHunter hasn't been on the site for about seven years now.

There's no issue with posting suggestions or solutions on such an old thread, but you should be aware the original participants are unlikely to benefit from it.

-Adrian (Access MVP and Site Moderator).