File Share Lock Count Exceeded - Only Occurs when table on back end

[mmogul]

I have a simple function (see code) that reads through an entire table and adds a sequence number to each record. When this function is run on a local table, it works fine. But when it runs against a linked table, I get the error:

"File share lock count exceeded. Increase MaxLocks per file registry entry."

The table I am updating has about 20,000 records.

As part of my troubleshooting, I created a new database and imported all objects into it. The same problem exists, so I do not believe I have a corrupt database.

Here is the code. Please let me know if there is something inherently wrong with it or linked tables require a different kind of approach. Thanks.

Public Function AddSequenceToTable(sTableName As String, sFieldName As String, Optional iStartValue As Long)
'Purpose: Adds a sequence number starting from 1 to the designated table and field
'
'CalledFrom:
'Calls  : basErrorHandler.ErrorHandler()
'Assumes: sTableName is the name of the table that is being updated
'         sFieldName is the name of the field on the table that contains the sequence number
'Written: MM
'Updated:
'Attribu: na
'Returns: True/False indicating success.
'------------------------------------------------------------------
'
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long

Set db = CurrentDb()
Set rs = db.OpenRecordset(sTableName, dbOpenDynaset)

If IsNull(iStartValue) Then
    iStartValue = 0
Else
End If


Do While Not rs.EOF
    iStartValue = iStartValue + 1
    With rs
        .Edit
            .Fields(sFieldName).Value = iStartValue
        .Update
        .MoveNext
    End With
Loop


On Error GoTo ErrorHandling:

ExitProc:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
  Exit Function

ErrorHandling:
  Select Case Err.Number
    Case Else
        Call ErrorHandler("frm.BasicUtils", Err.Number, Err.Description)
        GoTo ExitProc
  End Select



End Function

 

[genomon]

Try setting maxlocks to some ridiculous number and setting lockedits = false.

YourRS.LockEdits = False
DAO.DBEngine.SetOption dbMaxLocksPerFile, 30000

Beir bua agus beannacht!