borderAndreaVB free resources for Visual Basic developersborder

AndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2014 Andrea Tincani

AndreaVB Home | News Home | Forum Home | Downloads | Register | Search | PM | Profile

Previous Topic (array initialization)Next Topic (Finding a folder with VBA) New Topic Post Reply
AndreaVB OnLine : VB General : How to remove duplicate records through code
Poster Resource
ahmad
Level: Wizard

Registered: 03-02-2003
Posts: 111
icon How to remove duplicate records through code

Good Day
I have Microsoft Access 2003 at back end and VB6 at front end
need to delete duplicate records from a table.
The duplicate condition depends upon the matching of 4 fields
other then Primary Key.
e.g
Table S_T

id   Fld A   Fld B   Fld C   Fld E  
1     a         b        c        d
2     a         b                 d    
3     x         y        z        r
4     a         b        c        d
5     x         y        z        r
6     a         b        c        d

if the combination of field A,B,C and D is repeated then only one id should remain.Like  Id 1,4 and 6 are fulfilling the duplicate
condition i mentioned so ID 1 should remain and  4,6 should be deleted.Note id 2 has Fld C null so it is not fulfilling the duplicate condition.
id 3 and 5 are also fulfiling condition so id 3 is retained and 5 is deleted
Can anyone help how this could be done in VB 6

____________________________
@#@#@

28-11-2006 at 11:12 AM
View Profile Send Email to User Show All Posts Visit Homepage | Add Comment
admin
Level: Administrator


Registered: 04-04-2002
Posts: 532
icon Re: How to remove duplicate records through code

You can open two cursors on the same table and then for each record of the first cursor you can compare it with the second record, if all the fields are equal except ID, you can delete the record corresponding to second cursor ID. maybe then every time you delete a record you should close the two recordsets and then reopen them and restart looping.

let's try to write some code:

Dim rec1 as DAO.Recordset
Dim rec2 as DAO.Recordset

RestartLoop:
set rec1=mydb.OpenRecordset("myTablename")
set rec2=mydb.OpenRecordset("myTablename")
do while not rec1.eof
    do while not rec2.eof
        If rec1.Fields("FldA")=rec2.Fields("FldA") and _
            rec1.Fields("FldB")=rec2.Fields("FldB") and _
            rec1.Fields("FldC")=rec2.Fields("FldC") and _
            rec1.Fields("FldD")=rec2.Fields("FldD") and _
            rec1.Fields("ID")<>rec2.Fields("ID") Then
                rec2.Delete
                rec2.UpDate
                rec2.Close
                rec1.Close
                Goto RestartLoop
        End If
        rec2.MoveNext
    loop
    rec1.MoveNext
loop


The idea to resume loops is due to the fact that the recordsets are pointing to the same table, I've not tested it but maybe this is not necessary, please let me know if it works

____________________________
AndreaVB

28-11-2006 at 01:37 PM
View Profile Send Email to User Show All Posts Visit Homepage | Add Comment
ahmad
Level: Wizard

Registered: 03-02-2003
Posts: 111
icon Re: How to remove duplicate records through code


Hi
Sorry for the late response
i used that code and getting the following error
RUN TIME ERROR 3052
File SharingCount Exceed.Increase Maxlocks per file registry Entry.
Its Screen shot is attached with this message

My code is :
***************************************************
Private Sub cmdtest_Click()
Dim rec1 As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim mydb As Database
Dim strDbName As String
strDbName = "E:\john\Charlotte\Work\12-Dec\Project\Database\charlotte_mecklenburg_utilities.mdb"
  
Set mydb = OpenDatabase(strDbName)
RestartLoop:
Set rec1 = mydb.OpenRecordset("S_T")
Set rec2 = mydb.OpenRecordset("S_T")
Do While Not rec1.EOF
    Do While Not rec2.EOF
        If rec1.Fields("S_Date") = rec2.Fields("S_date") And _
            rec1.Fields("S_inspectionStartTime") =  rec2.Fields("S_inspectionStartTime") And _
            rec1.Fields("S_Street") = rec2.Fields("S_Street") And _
            rec1.Fields("S_SectionNumber") = rec2.Fields("S_SectionNumber") Then
            
                rec2.Delete
                'rec2.Update
                rec2.Close
                rec1.Close
                GoTo RestartLoop
        End If
        rec2.MoveNext
    Loop
    rec1.MoveNext
Loop

End Sub
******************************************************


____________________________
@#@#@

____________________________
:
error.jpg 17 KB (: 1)

12-12-2006 at 06:04 AM
View Profile Send Email to User Show All Posts Visit Homepage | Add Comment
admin
Level: Administrator


Registered: 04-04-2002
Posts: 532
icon Re: How to remove duplicate records through code

what line is giving you the error? why did you remove the rec2.Update code line?

when opening the recordset you can specify what type of lock to use for the recordset:

maybe we can use dbReadOnly for rec1 since we only need to read values from this recordset in order to compare them with rec2, then try to use dbOptimistic for rec2


Set rec1 = mydb.OpenRecordset("S_T", , , dbReadOnly)
Set rec2 = mydb.OpenRecordset("S_T", , , dbOptimistic)


____________________________
AndreaVB

12-12-2006 at 10:27 AM
View Profile Send Email to User Show All Posts Visit Homepage | Add Comment
AndreaVB OnLine : VB General : How to remove duplicate records through code
Previous Topic (array initialization)Next Topic (Finding a folder with VBA)New Topic Post Reply
Surf To:


Not Logged In? Username: Password: Lost your password?
borderAndreaVB free resources for Visual Basic developersborder
borderAndreaVB Visual Basic and VB.NET source code resources - Copyright © 1999-2014 Andrea Tincaniborder