CODEWITHSUNDEEP
vbams-accessfilterrecordset
Tralala
Tralala

Reputation: 293

Deleting from recordset

I have a sub that deletes the records in recordset2 based on the records of recordset1. The function works but very slow. Recordset1 has 300 records, Recordset2 73000 records.

Is there any way to speed this up?

Is it possible to use a filter, or a refiltered recordset?

Public Sub Erase()
    DoCmd.SetWarnings False

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
 
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("Tbl_1", dbOpenTable)2
    Set rs2 = db.OpenRecordset("KISS_2", dbOpenDynaset)2
    
    If rs1.RecordCount > 0 Then
        rs1.MoveLast
        rs1.MoveFirst
        lngCountRecordsRs1 = rs1.RecordCount
        
        Do Until rs1.EOF
            rs2.MoveFirst
            Do Until rs2.EOF
            If rs1!ID = rs2!ID Then
                    With rs2
                        .Delete
                    End With
            End If
            rs2.MoveNext
            Loop
        rs1.MoveNext
        Loop
    End If
    
    rs2.Close
    rs1.Close
    Set rs2 = Nothing
    Set rs1 = Nothing
    Set db = Nothing

    
Errorhandler:
End Sub

Upvotes: 0

Views: 3488

Answers (1)

Tralala
Tralala

Reputation: 293

Eventually I solved the problem with .FindFirst. Thanks for the input!

          With rs2
                .FindFirst "Id = " & rs1![ID]
                If rs2.NoMatch Then
                Else
                    .Delete
                    Exit Do
                End If
            End With

Upvotes: 1

Related Questions