mlcktmguy
asked on
File Sharing Lock Count Exceeded
I have logic that passes thru a local table with 300K records. What the routine does is simple but it keeps stopping with the 'File sharing locks exceeded' when I get to around record 10,000.
I did some research about this issue on EE and added the statement
DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000
At the top of the processing. There was no change, the error still occurred around record 10,000.
I also saw that registry revision is another way of handling this issue but that wouldn't work because I would have to do it on every user machine. There are close to 40 users.
When I first wrote the routine it processed all 300k records with no issues, this problem came later.
Since this is a local table there is no need for any locks on the table. No one but the current user will access it.
I pretty much use the same open statement on all of my ADO files. Is there something that I can do to make the open more suitable for this process. I don't know the effect of the options. Here is the open I use.
I tried both optimistic and pessimistic locking with no change.
Here is the processing loop:
Any ideas?
I did some research about this issue on EE and added the statement
DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000
At the top of the processing. There was no change, the error still occurred around record 10,000.
I also saw that registry revision is another way of handling this issue but that wouldn't work because I would have to do it on every user machine. There are close to 40 users.
When I first wrote the routine it processed all 300k records with no issues, this problem came later.
Since this is a local table there is no need for any locks on the table. No one but the current user will access it.
I pretty much use the same open statement on all of my ADO files. Is there something that I can do to make the open more suitable for this process. I don't know the effect of the options. Here is the open I use.
rsIn.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
I tried both optimistic and pessimistic locking with no change.
Here is the processing loop:
Dim CurrBRT As Long
Dim PrevBRT As Long
PrevBRT = 99999999
Dim BlockNum As Long
BlockNum = 0
'
Dim totRecs As Long
Dim recsRead As Long
Dim displayCount As Long
Dim displayMax As Long
Dim dispMsg As String
DAO.DBEngine.SetOption dbMaxLocksPerFile, 200000 ' or higher if necessary 'Microsoft DAO 3.6 Object Library
'wkDateStamp = Date
'wkDateTimeStamp = Now
'
selectString = "Select * from aSynch_Import_Raw " & _
" Order By [ID] "
'
Dim rsIn As ADODB.Recordset
Set rsIn = New ADODB.Recordset
rsIn.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockPessimistic 'adLockPessimistic 'adLockOptimistic
'
'Debug.Print selectString
'
If rsIn.EOF Then
MsgBox "No Payment Records to Process. Requested operation is terminated."
Exit Sub
Else
If rsIn.RecordCount > 0 Then
'
rsIn.MoveLast
rsIn.MoveFirst
'
totRecs = rsIn.RecordCount
displayMax = 50
'
While Not rsIn.EOF
'
recsRead = recsRead + 1
displayCount = displayCount + 1
If displayCount < displayMax Then
Else
dispMsg = "Processing Rec " & Trim(Str(recsRead)) & " Of " & Trim(Str(totRecs))
wkStatusRtn = SysCmd(acSysCmdSetStatus, dispMsg)
displayCount = 0
DoEvents
End If
DoEvents
'
CurrBRT = Nz(rsIn!BRT, 0)
'
If CurrBRT = PrevBRT Then
Else
BlockNum = BlockNum + 1
PrevBRT = CurrBRT
End If
'
rsIn!BlockID = BlockNum
'
' If Mid(Nz(rsIn!Period, 0), 5) = "1231" Then
rsIn!PrincipalNum = convertImportDbl2DecStrToNumber(Nz(rsIn!PrincipalStr, 0))
rsIn!InterestNum = convertImportDbl2DecStrToNumber(Nz(rsIn!InterestStr, 0))
rsIn!PenaltyNum = convertImportDbl2DecStrToNumber(Nz(rsIn!PenaltyStr, 0))
rsIn!OtherNum = convertImportDbl2DecStrToNumber(Nz(rsIn!OtherStr, 0))
rsIn!AttyFeesNum = convertImportDbl2DecStrToNumber(Nz(rsIn!AttyFeesStr, 0))
rsIn!LienNum = convertImportDbl2DecStrToNumber(Nz(rsIn!LienStr, 0))
rsIn!EligExpNum = 0
' Else
' rsIn!EligExpNum = convertImportDbl2DecStrToNumber(Nz(rsIn!OtherStr, 0))
' End If
'
' rsIn!SynchYear = Val(Mid(Nz(rsIn!Period, 0), 1, 4))
'
rsIn.Update
'
' write payments deactivated event record
'
rsIn.MoveNext
Wend
End If
End If
'
rsIn.Close
Set rsIn = Nothing
Stop
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
change the lock from pessimistic to optimistic
ASKER
Thanks for the responses, as I mentioned above I already tried switching between Optimistic and Pessimistic with no change.
jimpen:
First question: Would a user have to have a certain level of authority to execute the code that revises the registry? If so, it may work for me when I test it but won't work for all users in the production environment.
Is this code specific to operating system?
I cut and pasted your code into my app and got a runtime error. I attached the message
jimpen:
First question: Would a user have to have a certain level of authority to execute the code that revises the registry? If so, it may work for me when I test it but won't work for all users in the production environment.
Is this code specific to operating system?
I cut and pasted your code into my app and got a runtime error. I attached the message
ASKER
The specific line of code that threw the error was:
myWS.RegWrite i_RegKey, i_Value, i_Type
which is the last line in Sub RegKeySave
myWS.RegWrite i_RegKey, i_Value, i_Type
which is the last line in Sub RegKeySave
First question: Would a user have to have a certain level of authority to execute the code that revises the registry?
I can't tell you. All our users are local admins on their workstations.
But the error is because it's trying to use VBS to modify the registry on a Win7 machine.
I've been using it on XP/2K3 machines up until now.
I can't tell you. All our users are local admins on their workstations.
But the error is because it's trying to use VBS to modify the registry on a Win7 machine.
I've been using it on XP/2K3 machines up until now.
Since you are using ADO (right?), I'm not sure DAO.DBEngine.SetOption dbMaxLocksPerFile applies anyway. And if it's not working, then the Reg entry is not going to work either.
Not sure what is going on ... sorry.
mx
Not sure what is going on ... sorry.
mx
ASKER
Thanks for taking an interest in this.
Very strange because it will periodically run thru all the records without issue.
When I first put the routine together and tested it, it ran to completion.
I was doing some other testing today and by mistake I hit the button to process the 300K records. It ran to completion.
Tried it again and it threw the error at around rec 10,000.
Got out of the application re-booted the computer and it is throwing the error at 10,000.
I must be missing something obvious but I sure don't know what it is.
Very strange because it will periodically run thru all the records without issue.
When I first put the routine together and tested it, it ran to completion.
I was doing some other testing today and by mistake I hit the button to process the 300K records. It ran to completion.
Tried it again and it threw the error at around rec 10,000.
Got out of the application re-booted the computer and it is throwing the error at 10,000.
I must be missing something obvious but I sure don't know what it is.
Well for right now, just go ahead and edit the registry manually and see if it works. If it does then we can see if we can find an automated way.
ASKER
I had to set this aside temporarily due to higher priorities. Will follow up as soon as I get a chance.
Not a problem.
ASKER
Just wanted to keep you posted. I appreciate your help and interest in my question. I think we'll get there eventually.
ASKER
One possibly key difference is that in this case the loop involves only one table. I am passing thru a table and updating each records as I pass thru.
Very confused.