CODEWITHSUNDEEP
vbams-access
robbiecutting
robbiecutting

Reputation: 241

File sharing lock count exceeded

I keep getting an error message that 'File Sharing Lock Count has Exceeded.' There are workarounds to increase the registry each session or change the registry files but I don’t want users to have to go through that. Does anyone know why I might be getting such an error?

Heres my code:

Dim rst As DAO.Recordset
Dim rstCopy As DAO.Recordset
Dim Counter As Long

Set rst = dbs.openrecordset("SELECT * FROM [Qry_Calculate_Picking_Times]", dbopendynaset)
Set rstCopy = dbs.openrecordset("SELECT * FROM [Qry_Calculate_Picking_Times]", dbopendynaset)

rst.MoveLast
Counter = rst.RecordCount
rst.MoveFirst

rst.MoveNext
Counter = Counter - 1

While Counter > 0

With rst

If ![OWPPCK] <> rstCopy![OWPPCK] Or ![JustDate] <> rstCopy![JustDate] Or DateDiff("s", rstCopy![TIMESTAMP], ![TIMESTAMP]) > 3600 Then
    .Edit
    ![Time Difference Seconds] = Null
    .Update
Else
    .Edit
    ![Time Difference Seconds] = DateDiff("s", rstCopy![TIMESTAMP], ![TIMESTAMP])
    .Update
End If

If ![OWPFID] <> rstCopy![OWPFID] Then
    If ![OWPPCK] <> rstCopy![OWPPCK] Then
    Else
        .Edit
        ![NewLocation] = True
        .Update
    End If
End If

End With

rst.MoveNext
rstCopy.MoveNext

Counter = Counter - 1

Wend

rst.Close
rstCopy.Close

It seems to be at this stage the error occurs

    Else
       .Edit
       ![Time Difference Seconds] = DateDiff("s", rstCopy![TIMESTAMP], ![TIMESTAMP])
       .Update
    End If

Upvotes: 1

Views: 1513

Answers (1)

user3268137
user3268137

Reputation: 36

Not sure, but it appears that comparing the records of two recordsets like you're doing is causing the problem. It shouldn't, but we don't live in a perfect world.

What you're trying to accomplish should be easy enough to perform using only one recordset. Simply store the previous record in a variable before doing the movenext ... then compare to the current record. I've posted the code that I think would work.

Note: 1) I'm also storing the fields in the current recordset that you are comparing in variable for the sake of making the code easier to read and ... if you refer later to these field values, it should speed things up by not hitting the recordset multiple times for the same value (or so, I think.) 2) Current record variables are prefixed as "This..." and Previous record variables are prefixed as "Prev..." 3) I used a "Do Until ... Loop" instead of the "While ... wend", simply because that's my programming style.

Hope it works. Here it is:

Sub DoIt
    Dim rst As DAO.Recordset
    Dim ThisOWPPK, PrevOWPPK
    Dim ThisJustDate, PrevJustDate
    Dim ThisTIMESTAMP, PrevTIMESTAMP
    Tim ThisOWPFID, PrevOWPFID

    Set rst = dbs.openrecordset("SELECT * FROM [Qry_Calculate_Picking_Times]", dbopendynaset)

    rst.MoveFirst

    Do Until rst.EOF
        PrevOWPPCK = ![OWPPCK]
        PrevJUSTDate = ![JustDate]
        PrevTIMESTAMP = ![TIMESTAMP]
        PrevOWPFID = ![OWPDIF]

        rst.MoveNext
        ThisOWPPCK = ![OWPPCK]
        ThisJUSTDate = ![JustDate]
        ThisTIMESTAMP = ![TIMESTAMP]
        ThisOWPFID = ![OWPDIF]

        If ThisOWPPCK <> PrevOWPPCK Or ThisJustDate <> PrevJustDate Or DateDiff("s", PrevTIMESTAMP, ThisTIMESTAMP) > 3600 Then
            .Edit
            ![Time Difference Seconds] = Null
            .Update
        Else
            .Edit
            ![Time Difference Seconds] = DateDiff("s", PrevTIMESTAMP, ThisTIMESTAMP)
            .Update
            End If

            If ThisOWPFID <> PrevOWPFID Then
            If ThisOWPPCK <> PrevOWPPCK Then
                Else
                    .Edit
                    ![NewLocation] = True
                    .Update
            End If
        End If

    Loop

End Sub

Upvotes: 1

Related Questions