By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,676 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,676 IT Pros & Developers. It's quick & easy.

Find and delete duplicate records from a table

P: 77
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.
Expand|Select|Wrap|Line Numbers
  1. Function DeleteDuplicates_Click()
  2. On Error Resume Next
  3.  
  4.  Dim db As Database, rst As Recordset
  5.  Dim strDupName As String, strSaveName As String
  6.  Dim strSQL As String
  7.  strSQL = "SELECT [JOBNUM], [trnd] FROM Tableqryduplicates"
  8.  
  9.  Set db = CurrentDb()
  10.  Set rst = db.openrecordset(strSQL, dbOpenSnapshot)
  11.  
  12.  
  13.  If rst.BOF And rst.EOF Then
  14.    MsgBox "No records to process"
  15.  Else
  16.    rst.MoveFirst
  17.    Do Until rst.EOF
  18.      strDupName = rst.Fields("JOBNUM") & rst.Fields("trnd")
  19.  
  20.      If strDupName = strSaveName Then
  21.        rst.Delete
  22.      Else
  23.        strSaveName = rst.Fields("JOBNUM") & rst.Fields("trnd")
  24.      End If
  25.      rst.MoveNext
  26.    Loop
  27.  
  28.    rst.Close
  29.    Set rst = Nothing
  30.    Set db = Nothing
  31.  End If
  32.  
  33.  
  34. End Function
Nov 15 '07 #1
Share this Question
Share on Google+
7 Replies


Megalog
Expert 100+
P: 378
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"
Nov 15 '07 #2

NeoPa
Expert Mod 15k+
P: 31,276
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?
Nov 15 '07 #3

P: 77
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
Nov 16 '07 #4

NeoPa
Expert Mod 15k+
P: 31,276
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.
Nov 16 '07 #5

P: 77
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("case nbr") & rst.Fields("seq nbr") &
rst.Fields("trnd")
Get next row.
Else, if ptyp(associated with strDupName) is Null,
delete that row
strSaveName = rst.Fields("case nbr") & rst.Fields("seq nbr") &
rst.Fields("trnd")
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("case nbr") & rst.Fields("seq nbr") &
rst.Fields("trnd")
Get next row.
Else, if [p-lnam]+[p-fnam] (associated with strDupName) is Null,
delete that row
strSaveName = rst.Fields("case nbr") & rst.Fields("seq nbr") &
rst.Fields("trnd")
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("case nbr") & rst.Fields("seq nbr") &
rst.Fields("trnd")
Get next row.
Else, if [agency-name](associated with strDupName) is Null,
delete that row
strSaveName = rst.Fields("case nbr") & rst.Fields("seq nbr") &
rst.Fields("trnd")
Get next row
End If
End If

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

This is the code that works now,


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 [job nbr], [seq nbr], [trnd], [ptyp], [p-lnam], [p-fnam], [agency-name]FROM Tableqryduplicates"

Set db = CurrentDb()
Set rst = db.openrecordset(strSQL, dbOpenDynaset)

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

strDupName = rst.Fields("case nbr") & rst.Fields("seq nbr") & rst.Fields("trnd")

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

End If

rst.MoveNext
Loop

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

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

Thanks a lot.
Nov 16 '07 #6

P: 1
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 ...
Attached Files
File Type: zip Test.zip (33.5 KB, 94 views)
May 30 '16 #7

NeoPa
Expert Mod 15k+
P: 31,276
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).
May 30 '16 #8

Post your reply

Sign in to post your reply or Sign up for a free account.