How to deal with locking system in MS access safely in point of sales environment (1 Viewer)

[nectorch]

In a point of sales environment, you will find that when clients are purchasing some products maybe let say thirty lines now when the total amount to pay is shown to them, they may want to remove some lines not in the correct order but randomly. Doing that mean the line sequence will be distorted and so this causes Json to reject such kindly of lines sequencing.

So, to solve this problem I have put up the code below. It seems to be working ok

Auditing required

Just in case I missed something kindly check and advise. Here I'm also storing the line sequence in the child table which also benefit other reports.

Public Function RecalculateLineNumbers_RunSQL(p_ItemSoldID As Long)
    Dim db As DAO.Database
    Dim rsKeys As DAO.Recordset ' This RS only reads primary keys
    Dim strSQLFind As String
    Dim strSQLUpdate As String
    Dim i As Integer
    On Error GoTo ErrorHandler

    Set db = CurrentDb
    i = 1

    ' 1. Select the records just to iterate through their IDs (Use dbOpenSnapshot/read-only)
    strSQLFind = "SELECT [POSID] FROM [tblPosLineDetails] " & _
                 "WHERE [ItemSoldID] = " & p_ItemSoldID & " ORDER BY [POSID]"

    Set rsKeys = db.OpenRecordset(strSQLFind, dbOpenSnapshot) ' Snapshot is read-only

    If Not rsKeys.EOF Then
        rsKeys.MoveFirst
        Do Until rsKeys.EOF
            ' 2. Execute an immediate update query for the specific POSID using db.Execute
            strSQLUpdate = "UPDATE [tblPosLineDetails] SET [ItemesID] = " & i & _
                           " WHERE [POSID] = " & rsKeys![POSID]
            
            ' db.Execute runs the action query immediately without using the RS lock
            db.Execute strSQLUpdate, dbFailOnError
            
            i = i + 1
            rsKeys.MoveNext
        Loop
    End If

    rsKeys.Close
    Set rsKeys = Nothing
    Set db = Nothing
    Exit Function

ErrorHandler:
    MsgBox "Error " & Err.Number & " during SQL Update loop: " & Err.Description, vbCritical
    If Not rsKeys Is Nothing Then rsKeys.Close
    Set rsKeys = Nothing
    Set db = Nothing
End Function


Child form VBA required and also calling the function above

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = acDeleteOK Then
        If Me.Dirty Then Me.Dirty = False
        If Me.Parent.Dirty Then Me.Parent.Dirty = False
        
        ' Call the new SQL function
        Call RecalculateLineNumbers_RunSQL(Me.Parent!ItemSoldID)
        
        Me.Requery
    End If
End Sub

 

[CJ_London]

You don’t show how you are creating the json or the sql to your queries so not sure what to say.

Are you saying you will be parsing a query to create the json? And it is the line numbers that need to be consecutive? If so why not use a counter in the the code to create the line numbers at the time the json is being created?

 

[MarkK]

I would more likely just open a recordset and update it directly, rather that grab the key and execute an update query against the same row as is current in control recordset. Consider code like...

Public Function RecalculateLineNumbers_RunSQL(ItemSoldID As Long)
    Const SQL As String = _
        "SELECT ItemesID FROM tblPosLineDetails " & _
        "WHERE ItemSoldID = p0 " & _
        "ORDER BY POSID"
        
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = ItemSoldID
        UpdateRows .OpenRecordset
        .Close
    End With

End Function

Private Sub UpdateRows(rst As DAO.Recordset)
    With rst
        Do While Not .EOF
            .Edit
            !ItemesID = .AbsolutePosition + 1
            .Update
            .MoveNext
        Loop
        .Close
    End With
End Sub

Make sense? You are already traversing the recordset to get the keys. During that traversal, just update the current row directly.

 

[isladogs]

@nectorch
Are you and @nector the same person? If so, why are you using two separate accounts?

 

[nector]

Thank you all the respondents it looks like nothing is wrong here and from the test all is fine the people are working ok no issues at all

 

[Ken Sheridan]

Is it necessary to store the line numbers in the table, rather than just computing them in a query, from which the json can be generated? That way you can guarantee that they will always be sequential. The following is a simple query which returns subsets of row numbers per customer:

SELECT
    COUNT(*) AS RowNumber,
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (T2.CustomerID = T1.CustomerID)
    AND (T2.TransactionDate <= T1.TransactionDate)
    AND (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
GROUP BY
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount,
    T1.TransactionID;

In this Customer is analogous to Sale, Transaction to SaleItem, and TransactionDate to ItemID or similar. However, the above allows for duplicate TransactionDate values per customer, and brings the primary key TransactionID into play as the tie-breaker. In a Sales database this would not be the case, as the line numbers would be in distinct ItemID or similar order, so the query could be simplified to:

SELECT
    COUNT(*) AS RowNumber,
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (T2.CustomerID = T1.CustomerID)
    AND (T2.TransactionDate <= T1.TransactionDate)
GROUP BY
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount;