File Sharing Lock Count Exceeded

[James Franklin]

Hi,

I have an Access 2000 Front-End/Back-End app which has a piece of code in it
which when run cycles through a large number of records within a table on
the backend, making changes to many of them.

When the code runs however, I get error 3052: "File Sharing Lock Count
Exceeded. Increase MaxLocksPerFile registry entry."

I have done this, following MS Knowledge Base article
http://support.microsoft.com/default.aspx?scid=kb;en-us;815281#kb5

However can anyone tell me:
1) Can I increase this setting to any value?
2) What are the disadvantages of doing so?
3) Should this be done on the FrontEnd or BackEnd pc?
4) Is there any way of avoiding this problem, other than changing the
registry setting?
5) What actually causes this problem.

Many thanks to anyone who can help with any of the above!
Jim F.

 

[Guest]

Inside the loop, put this:

Application.dbengine.idle 1

Freeing locks is normally done as a low priority background
idle process. Explicitly calling the idle process gives it higher
priority.

(david)

 

[James Franklin]

Thanks David, but this did not seem to have any effect. Have I done
something wrong?

Here is my code. I want to renumber the records sequentially to remove gaps
that appear. There may be up to 100k records in the tbl_Sales table, there
is also a related tbl_Sales_Details table, which updates automtacally due to
referential integrity.

ws1.BeginTrans

Set rstS = db1.OpenRecordset("tbl_Sales", dbOpenDynaset,
dbDenyWrite)

'loop through Sales, renumbering the Sale_ID sequentially.
'Sales Detail records should update by relationship.
With rstS
If Not .EOF Then
.MoveFirst
NextSaleID = !Sale_ID + 1
.MoveNext

Do Until .EOF
If !Sale_ID <> NextSaleID Then
.Edit
!Sale_ID = NextSaleID
.Update
Application.DBEngine.Idle 1 'free any unneeded
file locks
End If

NextSaleID = NextSaleID + 1
.MoveNext
Loop
End If
.Close
End With

ws1.CommitTrans


Once again, thank you for helping, I appreciate it.
Jim