Inventory Calculations Multi-User Environment

Inventory software data management in a multi-user Microsoft Access environment must be able to take into account locking of data records.  Recordset edits should use error handling to prevent the loss of data when updating records that may be locked by other users of the database.

In this database example we use the Recordset Edit and After Update commands combined with error handling to check for general errors and also check for error number 3188, locked record.  If the program encounters any error then the edits are undone and the user is warned of the problem.

Access does not perform true record locking.  Instead Access locks a page of data on this disk.  The page contains approximately 2000 bytes of data.  An efficiently designed inventory table may have as many as 200 records locked at one time - this could represent a significant portion of a table depending on the amount of inventory records.

Here is the afterupdate event code we use:

Private Sub Qty_Out_AfterUpdate()
Dim db As DAO.database ' note that we always use DAO database and recordset operations because we always use Microsoft Jet databases.
Dim rst As DAO.Recordset ' DAO is simpler and faster so there is no reason for us to use ADO in Access. We use ADO in active server pages though. Set db = CurrentDb

On Error GoTo edit_error
Set rst = db.OpenRecordset("select inv_qty from m_inventory where barcode=" & Me.BarCode)

' note about locks on editted records: Access with lock 2000 bytes of the file. In our inventory table (M_Inventory) 2000 bytes could represent 10 to 20 records.
' so that another user does not have to be editting this same record but may be editing a record nearby and still cause our specific record to be locked.
With rst
    .Edit
    !Inv_Qty = !Inv_Qty + Nz(hold_qty, 0) ' in case this is a change to
            '    an existing order     quantity we back out the prior value.
    !Inv_Qty = !Inv_Qty - Nz(Me.Qty_Out, 0) ' decrement the
                inventory quantity based on the new qty value
    .Update
End With
Me.QOH.Requery
Exit Sub

' error handler follows
edit_error:
    If Err.Number = 3188 Then
        MsgBox "This record is locked by another user. Please try this edit again later."
        Me.Qty_Out = hold_qty ' return qty to original value
    Else
    MsgBox "An unexpected error occured while attempting the edit the data record: " & Err.Number & " " & Err.Description
    Me.Qty_Out = hold_qty ' return qty to original value
End If
Resume exit_out
exit_out:
End Sub

Use this link to download our Microsoft Access Multi-user Inventory Database.