Microsoft Access for Logistics Shipment Tracking: The Complete Guide

Written and reviewed by the Excel & Access Developers team — we design and build dispatch, fleet, and warehouse tracking databases for carriers, brokers, and 3PLs. 15+ years of hands-on Microsoft Access development. Last updated August 20, 2026.

Short answer: Yes. Microsoft Access can run a full logistics shipment tracking system—loads, stops, drivers, status history, and delivery exceptions—for small-to-midsize carriers, 3PLs, and private fleets that have outgrown Excel but don't need (or can't yet justify) a full transportation management system (TMS). Below is the exact table structure, working VBA, the honest technical limits, and when to walk away from Access entirely—so you can decide before you build anything.

Key Takeaways

• Access comfortably handles shipments, stops, and status events for a small dispatch team working from one office or network.
• Status belongs in an event-history table—never a single cell that gets overwritten all day.
• A split front end / back end is mandatory the moment dispatch and warehouse both touch the same loads.
• Access will not hand your customers a live public tracking link on its own—plan for that separately if it's a requirement.
• Most "Access is unreliable" stories in dispatch offices trace back to one unsplit file on Wi-Fi, not to Access itself.
• Access is not a complete TMS: no built-in carrier EDI, no driver mobile app, no rate engine.

If your current system is a shared Excel workbook of PRO numbers with a "Status" column everyone overwrites, you're already living the problem described in Excel spreadsheet vs Access database. For broader context on how we approach this space, see logistics solutions.

At a Glance: What Access Can (and Can't) Handle

Why Logistics Teams Still Use Microsoft Access for Shipment Tracking

Shipment work is relationship-heavy: one customer has many shipments, one shipment has many stops, one stop has an appointment window, and status changes constantly over time. Access models those relationships once, correctly. Spreadsheets flatten them until someone sorts the wrong column and loses which load was "Out for Delivery."

Familiar Windows tooling in the dispatch office

Most carriers already run Microsoft 365 on Windows. Dispatchers update forms they already understand; managers get exception and on-time reports—without licensing a separate browser-based TMS just for internal screens.

Custom fields without SaaS lock-in

Need a customer-specific POD checklist, a temperature flag, or a detention-minutes field no TMS template includes? In Access, that's a field, a form control, and a report column—not a support ticket to a vendor.

Fits small fleets and internal ops

Access is a strong fit for a small dispatch LAN (or managed remote desktop), a handful of concurrent editors, and internal visibility. When every shipper wants a branded portal and a mobile driver app, plan a TMS path deliberately rather than trying to bolt one onto Access. Scaling pitfalls to watch for: common mistakes when scaling an Access database.

One source of truth for status history

A proper StatusEvents table answers "when did this load actually leave the dock?" without digging through email threads, texts, or an overwritten spreadsheet cell.

The Core Data Model: Tables Every Shipment Tracker Needs

This is the schema we build for most carrier and 3PL clients. It scales from a five-truck operation to a multi-terminal fleet without a redesign.

AutoNumber primary keys throughout; foreign keys on the many side (`Shipments.CustomerID`, `Stops.ShipmentID`, `StatusEvents.ShipmentID`); referential integrity enforced so nobody can delete a shipment that still has status events attached. For the underlying relationship theory, see how to design an Access database and table relationships.

Logistics & Access Glossary

A quick reference for anyone mapping industry terms to database fields:

• PRO number — the shipment's public reference number; store it as its own field (`ProNumber`) separate from the internal AutoNumber `ShipmentID`.
• BOL (Bill of Lading) — the legal shipping document; usually just a reference number or file path in Access, not fully modeled.
• POD (Proof of Delivery) — signed confirmation of delivery; captured as a "Delivered" status event, often with a document reference attached.
• OS&D — Overage, Shortage, and Damage; the most common exception category on the Exceptions table.
• Appointment window — the scheduled pickup or delivery time range at a given Stop.
• Detention — time (and often cost) a driver accrues waiting past the appointment window.
• 3PL — a third-party logistics provider managing transportation on behalf of shippers.
• EDI — Electronic Data Interchange; the standardized messaging carriers and customers use to exchange load tenders and status updates automatically. This is the one piece Access does not handle natively.

Microsoft Access vs Excel for Shipment Tracking

Deeper spreadsheet limits: signs your business has outgrown Excel. Full platform comparison: Excel spreadsheet vs Access database.

Access vs a Dedicated TMS

Access is a database platform you shape yourself. A TMS is a finished product with EDI, optimization, and often a driver mobile app built in. Neither is universally better—it depends on what your operation actually needs.

Industry context: logistics solutions. Related operational patterns also show up in retail inventory management and manufacturing inventory system.

A Day in the Life: What This Looks Like in a Real Dispatch Office

Picture a twelve-truck regional carrier running dispatch out of Access. A load comes in by phone; dispatch opens the Shipments form, assigns a PRO number, and pulls the customer from a combo box tied to the Customers table. As the truck moves, dispatch posts status events—Dispatched, Picked Up, In Transit, Delivered—and each one lands as its own timestamped row in StatusEvents. If a load is running late, the LateFlag report catches it before the customer has to call and ask. If something goes wrong at a stop, an Exception status fires an email straight to the dispatch inbox automatically. None of this requires a TMS subscription—just a well-built Access front end talking to a back-end file sitting on the office server.

Common Problems Logistics Teams Hit With Access

Most failures trace back to file-sharing and design mistakes, not the software: one mega `.accdb` on Wi-Fi, no split, or a spreadsheet-shaped "loads" table where Status gets typed over all day long.

When something breaks:

• File won't open → [Access database will not open](/blog/access-database-wont-open-fix)
• Locked for editing → [Access database locked for editing](/blog/access-database-locked-for-editing-fix)
• Corruption → [corruption recovery](/blog/access-database-corruption-recovery) and [corruption prevention](/blog/prevent-access-database-from-corruption)
• Slow with years of events → [multi-user best practices](/blog/access-database-multi-user-best-practices) and [why Access slows with large data](/blog/why-access-slow-large-data)
• Growth mistakes → [scaling mistakes](/blog/common-mistakes-when-scaling-ms-access-database)

Split front end and back end

Data lives on a stable server share; each workstation runs its own front-end copy with forms, reports, and VBA. A broken linked-table path after a server move is a top cause of "the tracking system is down" mornings.

Concurrent dispatch and warehouse updates

Two people editing one unsplit file during pickup and delivery updates is exactly how locks and bad writes start. Split early, before it's a crisis.

Spreadsheet-shaped "databases"

One row with PRO, Customer, Origin, Dest, Status, Driver—and no event history—fails the first time someone needs to answer "when did the status actually change?" Separate the entities and link them with keys instead.

How to Build a Logistics Shipment Tracking Database in Access

Step 1: Define customers, shipments, stops, and status events as separate tables

Use the data model above as your starting point. Add Carriers and Exceptions only if you actually need them—don't build tables you won't populate.

Step 2: Set primary keys and relationships

AutoNumber keys (`CustomerID`, `ShipmentID`, `StopID`, `EventID`). Foreign keys on the many side. Enforce referential integrity so you can't delete a shipment that still has status events or stops attached.

Step 3: Build shipment and status forms

Main form = shipment header; subforms = stops and the status timeline. Require ShipmentID and StatusCode before posting an event. Never let users "edit history" casually—if a status was wrong, post a correcting event, don't overwrite the old one.

Open status history for the current shipment:

Private Sub cmdOpenStatusHistory_Click()
    On Error GoTo Handler

    If IsNull(Me!ShipmentID) Then
        MsgBox "Save the shipment record first.", vbExclamation
        Exit Sub
    End If

    DoCmd.OpenForm "frmStatusEvents", _
        WhereCondition:="ShipmentID = " & Me!ShipmentID

    Exit Sub
Handler:
    If Err.Number = 2501 Then
        Debug.Print "OpenForm canceled: " & Err.Description
    Else
        MsgBox "Could not open status history: " & Err.Number & " — " & Err.Description, vbCritical
    End If
End Sub

Post a status event and update current status (button on shipment form):

Private Sub cmdPostStatus_Click()
    On Error GoTo Handler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim statusCode As String

    If IsNull(Me!ShipmentID) Then
        MsgBox "Save the shipment first.", vbExclamation
        Exit Sub
    End If

    statusCode = Trim(Nz(Me!cboNewStatus, ""))
    If statusCode = "" Then
        MsgBox "Select a status.", vbExclamation
        Exit Sub
    End If

    Set db = CurrentDb
    Set rs = db.OpenRecordset("StatusEvents", dbOpenDynaset, dbAppendOnly)

    rs.AddNew
    rs!ShipmentID = Me!ShipmentID
    rs!StatusCode = statusCode
    rs!EventTime = Now
    rs!LocationNote = Nz(Me!txtStatusNote, "")
    rs!EnteredBy = Nz(Environ("USERNAME"), "unknown")
    rs.Update
    rs.Close

    ' Maintain a denormalized current status for fast lists/reports
    Me!CurrentStatus = statusCode
    Me!CurrentStatusTime = Now
    Me.Dirty = False

    ' Fire an exception alert if this status warrants one (see Step 5)
    Call NotifyExceptionByEmail(Me!ShipmentID, statusCode, Nz(Me!txtStatusNote, ""))

    Me!cboNewStatus = Null
    Me!txtStatusNote = Null
    MsgBox "Status posted: " & statusCode, vbInformation

    Exit Sub
Handler:
    MsgBox "Could not post status: " & Err.Number & " — " & Err.Description, vbCritical
End Sub

Step 4: Assign a driver and flag late shipments

Assign driver/vehicle from the dispatch form:

Private Sub cmdAssignDriver_Click()
    On Error GoTo Handler

    If IsNull(Me!ShipmentID) Then
        MsgBox "Save the shipment first.", vbExclamation
        Exit Sub
    End If

    If IsNull(Me!cboDriverID) Then
        MsgBox "Select a driver.", vbExclamation
        Exit Sub
    End If

    Me!DriverID = Me!cboDriverID
    Me!VehicleID = Me!cboVehicleID
    Me!DispatchedAt = Now
    Me.Dirty = False

    ' Optional: also write a Dispatched status event
    DoCmd.RunCommand acCmdSaveRecord
    Me!cboNewStatus = "Dispatched"
    Call cmdPostStatus_Click

    Exit Sub
Handler:
    MsgBox "Assign failed: " & Err.Number & " — " & Err.Description, vbCritical
End Sub

Flag shipments past promised delivery that are still not Delivered (admin button; test on a copy first):

Public Sub FlagLateShipments()
    On Error GoTo Handler

    Dim db As DAO.Database
    Dim sql As String

    Set db = CurrentDb

    sql = "UPDATE Shipments SET LateFlag = True " & _
          "WHERE Nz(CurrentStatus, '') <> 'Delivered' " & _
          "AND Nz(CurrentStatus, '') <> 'Cancelled' " & _
          "AND PromiseDeliveryDate < Date()"

    db.Execute sql, dbFailOnError
    MsgBox db.RecordsAffected & " shipment(s) flagged late.", vbInformation

    Exit Sub
Handler:
    MsgBox "FlagLateShipments failed: " & Err.Number & " — " & Err.Description, vbCritical
End Sub

Step 5: Auto-email dispatch when an exception is posted

This is the piece most Excel-based trackers can never do: a status update that proactively notifies someone instead of waiting to be noticed.

Public Sub NotifyExceptionByEmail(ByVal ShipmentID As Long, ByVal StatusCode As String, ByVal NoteText As String)
    On Error GoTo Handler

    Dim olApp As Object
    Dim olMail As Object
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim proNumber As String
    Dim custName As String

    ' Only fire for exception-type codes
    If StatusCode <> "Exception" And StatusCode <> "Refused" And StatusCode <> "Damaged" Then
        Exit Sub
    End If

    Set db = CurrentDb
    Set rs = db.OpenRecordset( _
        "SELECT S.ProNumber, C.CustomerName FROM Shipments S " & _
        "INNER JOIN Customers C ON S.CustomerID = C.CustomerID " & _
        "WHERE S.ShipmentID = " & ShipmentID, dbOpenSnapshot)

    If rs.EOF Then
        rs.Close
        Exit Sub
    End If

    proNumber = Nz(rs!ProNumber, "")
    custName = Nz(rs!CustomerName, "")
    rs.Close

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0) ' olMailItem

    With olMail
        .To = "dispatch@yourcompany.com"
        .Subject = "Shipment Exception: PRO " & proNumber & " (" & StatusCode & ")"
        .Body = "Customer: " & custName & vbCrLf & _
                "PRO #: " & proNumber & vbCrLf & _
                "Status: " & StatusCode & vbCrLf & _
                "Note: " & NoteText & vbCrLf & _
                "Posted: " & Now
        .Send
    End With

    Set olMail = Nothing
    Set olApp = Nothing
    Exit Sub

Handler:
    ' Don't let a failed email block status posting — log it instead
    Debug.Print "NotifyExceptionByEmail failed: " & Err.Number & " — " & Err.Description
End Sub

These are starter patterns—not a full TMS with rating, EDI, or optimization engines. Production fleets typically layer on appointment-compliance rules and richer exception workflows with a developer's help.

Reports and Dashboards Worth Building

Once StatusEvents is reliable, reporting is mostly query design:

• On-time delivery % — compare each shipment's "Delivered" event time against `PromiseDeliveryDate`, grouped by week or customer.
• Exceptions by reason — count StatusEvents rows by exception-type StatusCode to spot recurring problems by lane or customer.
• Driver / vehicle utilization — shipments per driver or unit over a period.
• Aging open shipments — anything without a "Delivered" event, sorted by days since dispatch, so nothing quietly falls through the cracks.
• Customer volume — shipment counts (and revenue, if you track linehaul rates) by customer and month.

Security, Backups, and Audit Trail Basics

A few things worth getting right from day one, since they're painful to retrofit:

• Permissions live at the application layer. Classic Access "user-level security" (workgroup `.mdw` files) only ever applied to the legacy `.mdb` format. A modern `.accdb` handles roles through your own Users/Roles table and a login form, or through file-system/network permissions—not built-in database security.
• Protect the audit trail. Nobody should be able to delete rows from StatusEvents. Enforce that through form and permission design, not an honor system.
• Back up the back-end file on a schedule, plus keep a versioned copy before any schema change. A split architecture makes this easy since the back end is a single file.
• Treat driver and customer PII carefully. License numbers, phone numbers, and billing details deserve restricted forms and, if the file leaves the office network, encryption or a password.

Choosing Between Access and a TMS: A Quick Checklist

Access is a good fit when:

• A small, defined dispatch team (roughly 2–15 people) needs shared visibility into loads
• You want full control over fields and workflow without per-shipment SaaS fees
• You're moving off Excel and need real status history, not just a current snapshot
• Someone in-house (or a developer relationship) can maintain a split front-end/back-end setup

A TMS is a better fit when:

• Customers expect a self-serve tracking portal or API access
• You need carrier EDI/API integrations, rate shopping, or load optimization
• You have many concurrent users across multiple terminals or locations
• Nobody can own ongoing VBA/forms maintenance

When to Bring in a Developer / Upgrade Your System

Stay DIY when one dispatcher owns the file, volume is low, and a wrong weekend experiment won't strand freight.

Bring in a developer when dispatch and warehouse must edit together, status history has to be trustworthy, or you're migrating off Excel and can't afford to lose data in the process. Start with Access database design and development or Access development.

Plan a TMS upgrade when customers need live portals, you need carrier EDI at scale, or concurrent users outgrow a well-split Access app. Some teams keep Access for internal ops while a TMS handles customer-facing tracking—just avoid two conflicting sources of truth for the same PRO number.

Related reading: retail inventory management case study (movement/ops patterns) and manufacturing inventory system.

Frequently Asked Questions

Next Steps

Map customers → shipments → stops → status events on paper, then decide whether you're building a focused Access tracker or evaluating a TMS. Start with logistics solutions. Want it built correctly the first time? Contact us for a free consultation.