exceeding MaxLocksPerFile

[ckib]

Hi all,

Latest challenge ... when a new project is launched, the code below is executed, and fails because the found set has 15,000 records. I followed the directions here:
http://support.microsoft.com/default.aspx?id=815281

for using the immediate window and the SetOption method to temporarily change the MaxLocksPerFile value, but I received an error. Is there any instruction that I could add to my code just before the large update to modify this setting? I'm unnerved by the warnings for editing the registry key! Thanks for any advice. Here is the code:

' now update tblSegment with the correct segment number for each contact

intSegmentNum = Me!proj_segments ' initial value = max segment number

theLoopCount = lngRemainder / intSegmentNum ' calculate number of contacts per segment
' VBA will round up - handled by last loop execution

intSegSize = theLoopCount ' save copy of segment size

strSQL = "SELECT * FROM tblSegment " & _
"WHERE (((tblSegment.seg_projectID)='" & strProjectID & "'));"

Set myRS = myDB.OpenRecordset(strSQL)

myRS.MoveFirst

Do While intSegmentNum > 0 ' do for each segment
Do While theLoopCount > 0 ' do for each contact within this segment
myRS.Edit
myRS!seg_num = intSegmentNum
myRS.Update
theLoopCount = theLoopCount - 1
myRS.MoveNext
Loop

lngRemainder = lngRemainder - intSegSize ' contacts remaining after each segment loop
intSegmentNum = intSegmentNum - 1 ' decrement segment number for next loop
If intSegmentNum = 1 Then ' if next loop is last segment
theLoopCount = lngRemainder ' use remainder
Else
theLoopCount = intSegSize ' initialize loop counter for next segment
End If

Loop

myRS.Close

 

[CT Witter]

Maybe you could wrap your updates in a batch transaction? See my post at: http://www.mrexcel.com/board2/viewtopic.php?t=80017
for an example of a transaction update. Transactions are not only faster since you are commiting less writes (the data is updated in memory, then written to disk all at once) but also give you some safety in doing the update in case of errors/failures.

HTH,
CT

 

[ckib]

Thanks. I will try that.

One more question ... what exactly is the difference between coding

myRS.Close and set myRS = nothing

Thanks.

 

[CT Witter]

myRS.Close -- Closes the Recordset
Set myRS = nothing -- Reclaims the memory the recordset was using

Ideally Access should handle the clean up and releasing of memory for you, but good programmers always clean up after themseleves.

HTH,
CT

 

[ckib]

I re-coded using a transaction in an attempt to avoid exceeding MaxLocksPerFile, but still hit the limit.

There must be a solution to this! Or is the only way around it to modify the registry??? I can't believe that there is a 9500 limit to the number of records you can update in a loop ...

Thanks for any ideas anyone may have ...

 

[ckib]

In case any one else needs a solution to this ... I needed to commit the transactions inside the inner loop:

myRS.MoveFirst

Do While intSegmentNum > 0 ' do for each segment
wrk.BeginTrans ' start the transaction
Do While theLoopCount > 0 ' do for each contact within this segment
myRS.Edit
myRS!seg_num = intSegmentNum
myRS.Update
theLoopCount = theLoopCount - 1
myRS.MoveNext
Loop
wrk.CommitTrans ' loop complete for this segment, commit the updates

lngRemainder = lngRemainder - intSegSize ' contacts remaining after each segment loop
intSegmentNum = intSegmentNum - 1 ' decrement segment number for next loop
If intSegmentNum = 1 Then ' if next loop is last segment
theLoopCount = lngRemainder ' use remainder
Else
theLoopCount = intSegSize ' initialize loop counter for next segment
End If

Loop

myRS.Close
Set myRS = Nothing