Delete consecutive duplicates: "File sharing lock count exceeded" at 9k of 1m records

Question

I need to remove consecutive duplicate records from a large table (It could contain 200k-1 million records, and about half the records will be deleted).

DateTime is unique. Records where the remaining fields match the previous record, should be deleted, leaving the "first" record. (Non-consecutive duplicates should stay.)

       DateTime        Field1   Field2   Field3  
 -------------------- -------- -------- -------- 
  2018-09-01 5:20:33      123      456      789  
  2018-09-01 5:20:34      123      456      789        ← delete
  2018-09-01 5:20:35      123      654      987  
  2018-09-01 5:20:36      234      567      890  
  2018-09-01 5:20:37      234      567      890        ← delete 
  2018-09-01 5:20:38      234      567      890        ← delete 
  2018-09-01 5:20:39      123      456      789                     

I'm iterating through the recordset (ordered by date) to delete duplicates, but when I hit about 9,500 deletions I get:

Error 3052: File sharing lock count exceeded.  
            Increase MaxLocksPerFile registry entry.

As per these instructions, I could increase the value in the registry, or with the SetOption method, however I'm not clear on the impact of doing so since it appears I'd need to increase the value from 9,500 to at least 500,000 or more... I'm not sure, but this seems like a bad idea.

---

Simplified version of my code:

Sub example_DelDupes()
    Dim rs As Recordset, delCount As Long, rCount as long
    Dim thisRecord As String, prevRecord As String
    Set rs = CurrentDb.OpenRecordset("select * from myTable order by DateTime")
    With rs
        .MoveLast            'so I can get a recordcount...
        .MoveFirst
        rCount = .RecordCount '...required for progress bar (not shown)

        Do While Not .EOF
            thisRecord = !field1$ & !field2$ & !field3$ 'actually 
            If thisRecord = prevRecord Then
                .Delete                      'delete this record
                delCount = delCount + 1
            Else
                prevRecord = thisRecord
            End If
            .MoveNext
        Loop

        .Close
    End With
    Set rs = Nothing
End Sub

I thought that deleting from the set I'm iterating could be the issue, so instead of .Delete I also tried marking the records to be deleted, with the intention of deleting them after the loop completes, but editing produced the same error as deleting.

---

I suspect there may be a better way to accomplish my task, perhaps by opening the recordset with a different LockTypeEnum, or by approaching this a different way altogether, but I'm hoping to avoid trial-and-error.

Thoughts?   Thanks!  

Answer 1

I'd delete everything in a single query instead of iterating over the recordset.

You can use a nested subquery to test if the row before satisfies your condition:

DELETE o.*
FROM MyTable o
WHERE EXISTS(
    SELECT 1 
    FROM MyTable i 
    WHERE o.Field1 = i.Field1 And o.Field2 = i.Field2 AND o.Field3 = i.Field3 
    AND EXISTS (
        SELECT  1
        FROM MyTable i2 
        WHERE i2.DateTime < o.DateTime
        HAVING Max(i2.DateTime) = i.DateTime
    )
)