File Sharing Lockout Count Exceeded error 1

[hcisteve]

I am trying to manually create a sequentially numbered key using the code

Dim myRecordSet As New ADODB.Recordset
Dim rs As DAO.Recordset
myRecordSet.ActiveConnection = cnn1
Dim SQL1, SQL2, Statement As String
myRecordSet.LockType = adLockOptimistic
myRecordSet.Open "SurveyTrackerTest"
maxnum = DCount("*", "SurveyTrackerTest")
myRecordSet.MoveFirst
For i = 1 To maxnum
myRecordSet.Fields.Item("ind2").Value = i
myRecordSet.MoveNext
Next i

I get the error message File Sharing Lockout Count Exceeded after about 9400 records? What can I do to work around this?

 

[VBslammer]

See if this thread helps:

MaxLocksPerFile error (3052)

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[hcisteve]

Thanks. I tried adding the temp code you recommended:

DAO.DBEngine.SetOption DAO.dbMaxLocksPerFile, 1400

into my program and it didn't seem to help. In fact when I hovered over the value DAO.dbMaxLocksPerFile it said it was 62. Very strange.

 

[VBslammer]

Access doesn't provide a way to get the number of locks, aside from using the API to check the registry setting. If you use the SetOption method to set the locks there is no way at all to tell what the temporary setting is, but since you are setting it, you should know what it is .

The number 62 is the value of the dbMaxLocksPerFile constant - not the setting.

You can try to manually edit the registry to see if that will raise the lock limit. I normally use SetOption with the number of records in the recordset, such as: [tt]SetOption dbMaxLocksPerFile, maxnum[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[hcisteve]

Thanks again. I tried again using the

DAO.DBEngine.SetOption DAO.dbMaxLocksPerFile, maxnum

and it still didn't work. The strange thing is that no matter what I do the loop fails when i is 9492.

 

[VBslammer]

Are you calling the Update() method after each change?
[tt]
For i = 1 To maxnum
With myRecordSet
.Fields("ind2").Value = i
.Update
.MoveNext
End With
Next i
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[willir]

Could it be that you are defining DAO record locks, but using ADO to walk through the table??

DAO.DBEngine.SetOption DAO.dbMaxLocksPerFile, maxnum
'vs
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
myRecordSet.LockType = adLockOptimistic
maxnum = DCount("*", "SurveyTrackerTest")

myRecordSet.MoveFirst

For i = 1 To maxnum
   myRecordSet.Fields.Item("ind2").Value = i
   myRecordSet.Update
   myRecordSet.MoveNext
Next i

And VBSlammer is quite correct in that you need to update each record before proceeding to the next.

 

[hcisteve]

No. I wasn't. That solved the problem. Thanks!!

 

[hcisteve]

I reacted prematurely and without proper testing because I thought for sure that the recommendation above would solve my problem. I appologize for that. Although not having an update was one of my problems, I was still getting the error message. What I did to solve the problem was to save the absolute position in a variable and then close and reopen the recordset, going back to the absolute postion +1. I chose every 9000 records as the time to do this because it was failing about every 9400 records.


Dim strSQL As String
Dim Position As Long
strSQL = "SELECT ColumnName FROM Table"
Top:
With CurrentDb.OpenRecordset(strSQL)
If Position > 1 Then .Move Position + 1
Do Until .EOF

.
.
.
Position = .AbsolutePosition
.Edit
.Fields("ColumnName") = ValueFromOtherCode
.Update
.MoveNext
If (Position > 1) And (Position Mod 9000 = 0) Then
.Close
GoTo Top
End If
Loop

End With

End Sub

 

[VBslammer]

I thought about doing that too. There is also another method that supposedly refreshes any impending transactions and allows the Jet engine to unload any uneeded read locks:

[tt]
DBEngine.Idle dbRefreshCache
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob