MS Access Inventory System Problems: Why Stock Counts Are Wrong and How to Fix Them

Stock Discrepancies

Why MS Access inventory stock counts are wrong — and how to reconcile them

Treat an inventory stock discrepancy in MS Access as a ledger reconciliation problem, not a data-entry problem. The screen says twelve; the shelf says nine; finance says fifteen. All three numbers can be wrong simultaneously when the database has been patched instead of fixed.

The diagnostic step is simple: run a query that compares your on-hand field to the sum of your movement ledger per SKU. Any SKU where those two numbers differ is drifted. The SQL sample in the Transaction Integrity section below does exactly this. Run it before touching anything else.

The fix is to insert an adjustment movement row — a signed quantity correction — rather than editing the on-hand cell directly. This preserves the audit trail and lets you trace the correction at any future point. Editing the cell directly is how the discrepancy grew in the first place.

Record Locking

Write conflicts and the .laccdb file — what they mean and how to fix them

When two users in a US warehouse or office hit "Write Conflict: This record has been changed by another user" on an inventory form, the symptom is a concurrency problem but the root cause is architecture. Bound Access forms hold rows open across navigation, extending exclusive locks longer than necessary. On a slow LAN or VPN, that lock window extends further — long enough for the second user's save to collide.

The standard fix sequence: (1) confirm the database is split into a separate front-end and back-end file — this alone reduces most random locking; (2) switch high-contention forms from Edited Record locking to No Locks with a VBA save handler that checks for conflicts explicitly; (3) shorten transactions so the lock scope is milliseconds, not seconds; (4) for hot SKUs updated by many users simultaneously, serialize writes through a queue table or move that tier to SQL Server.

Multi-User Scaling

How many concurrent users can update MS Access inventory at the same time?

File-backed MS Access works reliably for inventory systems with up to 10–15 concurrent users when the FE/BE is split, forms use appropriate locking, and indexes match the query paths. Beyond that, performance degrades and write conflicts become frequent enough to damage operations.

For US and Canadian businesses with growing teams, the most common and cost-effective path is an Access front-end / SQL Server back-end architecture. The Access forms, reports, and VBA that your team already knows stay in place; the data moves to SQL Server, which provides row-level locking, proper transaction logs, and the ability to support 50–100+ concurrent users without performance issues. This is far cheaper and less disruptive than a full platform migration.

Transaction Integrity

Fixing inventory transaction errors in MS Access — one event, one atomic footprint

A sale, transfer, or receipt is one business event. It should write to the header table and the stock movement table in a single atomic operation — either both rows commit, or neither does. When VBA processes them as two separate steps without a workspace transaction and error handler, a crash between the two writes creates an orphaned record that permanently drifts your counts.

The correct pattern: DBEngine.Workspaces(0).BeginTrans before the first write, CommitTrans after both succeed, and Rollback in the error handler. Never use append-only movement inserts that rely on form events firing in the correct order without explicit transaction wrapping.

Use this SQL to find SKUs where on-hand already differs from the movement ledger sum. Run it on a backup copy before making any changes:

-- Find SKUs where on-hand quantity ≠ movement ledger total
-- Rename tblInventory, tblInventoryMovements, SKU, QtyOnHand, QtyDelta
-- to match your actual table and field names before running.
SELECT
  i.SKU,
  i.QtyOnHand                    AS OnHand,
  Nz(s.LedgerNet, 0)             AS LedgerNet,
  [QtyOnHand] - Nz([LedgerNet], 0) AS Drift
FROM tblInventory AS i
LEFT JOIN (
  SELECT SKU, Sum(QtyDelta) AS LedgerNet
  FROM   tblInventoryMovements
  GROUP  BY SKU
) AS s ON i.SKU = s.SKU
WHERE [QtyOnHand] <> Nz([LedgerNet], 0)
   OR (s.SKU Is Null AND i.QtyOnHand <> 0);

LIFO / FIFO Logic Errors

Why COGS swings randomly and how to fix cost-layer allocation in MS Access

FIFO and LIFO are not sort orders — they are consumption algorithms applied against a cost-layer table. The most common MS Access FIFO/LIFO errors occur when:

• Picks can split across multiple receipts but the allocation query does not correctly consume layers in date order, leaving fractional quantities assigned twice or not at all.
• The system uses an average-cost shortcut in some reports and a layer table in others — the two figures can never reconcile without an audit.
• Adjustments and returns are posted directly to on-hand without a corresponding layer entry, causing the layer sum to diverge from on-hand after every adjustment cycle.

The fix requires a dedicated inventory layers table where each receipt row records its cost, date, quantity received, and quantity remaining. Every pick allocates against layers in the correct order and updates the remaining-quantity field on the layer row. A post-pick reconciliation check confirms that the sum of remaining layer quantities matches on-hand before the transaction commits.

Step-by-Step Fix

How to fix MS Access inventory stock discrepancy — 6 steps

This is the exact sequence we use when a US or Canadian business calls us with inventory counts that don't match. Follow it on a copy of your database — never on the live production file.

1. Run the drift reconciliation query. Export the results to a spreadsheet. Every drifted SKU needs a root cause identified before any fix is applied.
2. Identify the source of each drift. For each drifted SKU, check the movement history: were headers deleted without reversing stock? Did an import fail halfway? Were on-hand cells edited directly?
3. Insert adjustment movement rows — do not edit on-hand directly. For each drifted SKU, insert a signed adjustment row (+/−) equal to the drift amount. Mark it with a movement type of "Audit Adjustment" and today's date.
4. Add referential integrity to prevent orphaned records. Enable cascade delete or restrict delete on the relationship between your header tables and movement tables so future deletions cannot orphan stock records.
5. Index the columns you query most. Add indexes to SKU, transaction date, and movement type. Run your month-end report again and compare the query time.
6. Schedule a weekly automated reconciliation check. Set up a VBA autoexec or scheduled task that runs the drift query and logs any drifted SKUs to a monitoring table. Catch drift weekly instead of discovering it at the annual audit.

Inventory Audit Protocol

Freeze, snapshot, reconcile, ship — the right order for an MS Access inventory audit

Running an inventory audit on a live MS Access database while users are still posting transactions guarantees inconsistent results. The correct sequence:

1. Freeze all transactions during the audit window — no receipts, sales, or transfers posted.
2. Take a timestamped backup of the back-end file before running any reconciliation queries. This is your rollback point.
3. Run the drift SQL and export results. Investigate every discrepancy before inserting adjustment rows.
4. Insert adjustment rows with audit notation — movement type, date, and the person authorizing the correction.
5. Run the drift SQL again to confirm all SKUs now reconcile before re-opening transactions.

For databases with engine-level corruption (compact-and-repair fails, tables won't open, records are missing), route through MS Access database repair before trusting any inventory count.