I'm trying to write some code to help the forum member on this thread. When I run the code that follows it errors on the line rs.Edit with the error message:
In googling this error all I'm finding is recommendations to change the MaxLocksPerFile in the registry. I'm using Access 2013 and Windows 7 and this entry is set to 9500. That just about where the code quits. After updating about 9400 and some records. But increasing this number isn't a solution as this needs to work with over a million records. I don't understand why the lock on the record isn't release when it updated. What am I missing in this code (database attached)
In googling this error all I'm finding is recommendations to change the MaxLocksPerFile in the registry. I'm using Access 2013 and Windows 7 and this entry is set to 9500. That just about where the code quits. After updating about 9400 and some records. But increasing this number isn't a solution as this needs to work with over a million records. I don't understand why the lock on the record isn't release when it updated. What am I missing in this code (database attached)
Code:
Public Sub UpdatePreviousValues()
Dim rs As DAO.Recordset
Dim varPreviousValue As Variant
Dim strPreviousAccount As String
Set rs = CurrentDb.OpenRecordset("qrySourceSorted")
'if there are 0 - 1 records nothing is done
If rs.EOF And rs.BOF Then
Exit Sub
Else
rs.MoveLast
If rs.RecordCount < 3 Then
Exit Sub
End If
rs.MoveFirst
End If
strPreviousAccount = rs!Account
varPreviousValue = rs!Current_Value
rs.MoveNext
Do While Not rs.EOF
If rs!Account = strPreviousAccount Then
rs.Edit
rs!PreviousValue = varPreviousValue
rs.Update
End If
strPreviousAccount = rs!Account
varPreviousValue = rs!Current_Value
rs.MoveNext
Loop
rs.Close
End Sub