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

____________________________
@#@#@

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

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
******************************************************


____________________________
@#@#@

____________________________
Attached:
error.jpg 17 KB (Downloads: 1)

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