After compacting the database I run code and get the error-MaxLocksPerFile registry

[Linda Ribbach]

I was running an code to loop through 14,000 trans numbers and number each line 1,2,3, etc until it sees a new trans number. Everything was working just fine until I compacted the database. Now I'm getting this error and my code stops running.

I still have several Mdb files that I haven't compacted and code still works. But does anyone know how I can fix this. I've expanded the registry to 20000, but that didn't help.

Also here's the code I'm using: the bolded area is where it stops.

Thanks in advance

Public Function countRecord(tableName As String, mrecord As String, LINE_NO As String)

Dim db As Database
Dim rs As Recordset
Dim counter As Integer
Dim r As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM " & tableName & " ORDER BY " & mrecord)
If rs.EOF Then
Exit Function
End If

counter = 1
rs.MoveFirst
rs.Edit
rs(LINE_NO) = counter
rs.Update
r = rs(mrecord)

counter = 1
rs.MoveNext
Do While Not rs.EOF

If Not IsNull(rs(mrecord)) Then
If (rs(mrecord) = r) Then

rs.Edit
counter = counter + 1
rs(LINE_NO) = counter
rs.Update

Else
counter = 1
rs.Edit
rs(LINE_NO) = counter
rs.Update
End If
End If
r = rs(mrecord)
rs.MoveNext

Loop

End Function