File Sharing Lock COunt Exceeded Issue

[jeff]

gday all

Scenario:
Access v11 SP2 (Office 2003)
2 Access tables in application database (not linked)
Table1 has approx 450,000 records and Table2 has approx 310,000 records
2 subs each the same but processing the 2 tables.
The first one works AOK, the second returns the error:
Error# 3052 was generated by DAO.Recordset
File sharing lock count exceeded. Increase MaxLocksPerFile registry
entry.

Code snippets:
- This one works perfectly all the way through the 450,000 records.
Public Sub subWireSequencing()
Dim ldbsCurrent As Database
Dim lrstSource As Recordset
Dim strSQL As String, lstrCurrentCable As String
Dim intNewWireSequence As Integer
Set ldbsCurrent = CurrentDb()
strSQL = "SELECT * FROM CableSetWires " & _
"ORDER BY CABLEID, WIRESEQUENCE, SEQUENCE;"
Set lrstSource = ldbsCurrent.OpenRecordset(strSQL, dbOpenDynaset)
lrstSource.MoveFirst
lstrCurrentCable = ""
intNewWireSequence = 0
' process each record
Do While Not lrstSource.EOF
' if start of a new cable record then reset the sequencing
If lstrCurrentCable <> lrstSource.Fields("CABLEID").Value Then
lstrCurrentCable = lrstSource.Fields("CABLEID").Value
intNewWireSequence = 0
End If
' increment the current wire sequence
intNewWireSequence = intNewWireSequence + 1
' update the wire sequence with the new sequence number
lrstSource.Edit
lrstSource.Fields("WIRESEQUENCE").Value = intNewWireSequence
lrstSource.Update
lrstSource.MoveNext
Loop
lrstSource.Close

- This one fails at around ~9500 records
Public Sub subWireSetSequencing()
Dim ldbsCurrent As Database
Dim lrstSource As Recordset
Dim strSQL As String, lstrCurrentCable As String
Dim intNewSetSequence As Integer
Set ldbsCurrent = CurrentDb()
strSQL = "SELECT * FROM CableSets " & _
"ORDER BY CABLEID, SEQUENCE, SETSEQUENCE;"
Set lrstSource = ldbsCurrent.OpenRecordset(strSQL, dbOpenDynaset)
lrstSource.MoveFirst
lstrCurrentCable = ""
intNewSetSequence = 0
' process each record
Do While Not lrstSource.EOF
' if start of a new cable record then reset the sequencing
If lstrCurrentCable <> lrstSource.Fields("CABLEID").Value Then
lstrCurrentCable = lrstSource.Fields("CABLEID").Value
intNewSetSequence = 0
End If
' increment the current Set sequence
intNewSetSequence = intNewSetSequence + 1
' update the Set sequence with the new sequence number
lrstSource.Edit
lrstSource.Fields("SETSEQUENCE").Value = intNewSetSequence
lrstSource.Update
lrstSource.MoveNext
Loop
lrstSource.Close

As can be seen....almost identical code...different tables...it has got be
munted! Can anyone help????

cheers
Jeff

 

[Tom Wickerath]

Hi Jeff,

I have no explaination for why your first procedure works on 450,000
records, while your second procedure chokes at approx. 9500 records, other
than you are apparently exceeding the MaxLocksPerFile setting for whatever
reason. Try one of the resolutions shown in this KB article:

"File sharing lock count exceeded…†error message during
large transaction processing
http://support.microsoft.com/?id=815281


What type of objects are CableSets and CableSetWires, ie. table or query?
If CableSets is a query, one possibility is that the Record Locks property
for this query is set to All Records, but the same property is set to Edited
Record for CableSetWires.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Hi Jeff,

I thought of another possibility. Does the CableSets table include a memo,
hyperlink or OLE Object field? If so, that will prevent record-level locking
(according to a Microsoft KB article). I would think that it would still only
lock a page of records at a time, but who knows.

I attached the table defn in pdf if interested...

It's not a good idea to include any file attachments to newsgroups, unless
the group's charter specifically permits it. In any case, while I can see
your message in the MS Web tool:

http://www.microsoft.com/office/com...ovba&mid=f2ae5079-3e93-4e30-89ac-4b3b01d18186

I am not finding the same message when I use Outlook Express as my
newsreader. I currently have OE configured to use the Comcast server, so that
likely explains why the difference.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

Okay, I configured OE to use news.microsoft.com, and I was now able to see
the message that you mentioned with the file attachment. Nope, didn't see any
memo, hyperlink or OLE Object fields in that table's schema.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html