How to Optimize MS Access Performance: The Enterprise Guide to Database Speed & Stability.

Why Access feels “slow” on modern VPNs

The hybrid work reality for US, UK & Canada teams

When staff shifted from head-office LANs to home broadband behind corporate VPNs, Access did not get slower—your round-trip time increased. Every linked-table read became a small SMB conversation across the WAN, and every chatty form multiplied that cost. The fix is not “buy a faster laptop.” The fix is Persistent Connections, Linked Table Manager discipline, colocated back-ends or RDP session hosts, and ruthless query bounding—exactly the patterns enterprise teams use to speed up MS Access over VPN.

Infrastructure audit

Measure before you tune

An enterprise audit inventories where Access actually runs: endpoint versions (32/64-bit), Office channel, back-end path, share protocol (SMB version), VPN profile, and antivirus hooks. Each one can turn a 0.3-second query into a 9-second stall—especially when remote colleagues route through a single on-prem concentrator.

• Log object timings with a lightweight instrumentation stub; no guesswork.
• Confirm SMB 3.x, not legacy SMB 1 or CIFS tuning ghosts.
• Record bitness, Office channel, and whether runtime vs full Access is deployed.
• Treat .laccdb churn as signal, not folklore.

The FE/BE split

Architecture first for multi-user US / UK teams

The Front-End / Back-End split is not optional for multi-user deployments. Each user runs their own FE on a local drive; the BE holds durable data on a file share or—better—SQL Server. Linked tables connect the two, and a Persistent Connection stops the engine from reopening the back-end for every trivial read.

• Deploy the FE via packaged installer or managed share copy—not by hand-passing .accdb files.
• Use the Linked Table Manager to manage back-end paths per environment (LAN, RDP, SQL).
• Keep back-end file shares geographically close to writers; colocate an RDP host when users are distributed.

Query plan optimization

Beyond Compact & Repair

Compact and Repair reclaims pages; it does not rewrite bad queries. Enterprise optimization proves indexes on every join column, kills wildcard prefix searches on large text fields, narrows SELECT lists to what the UI actually binds, and moves heavy aggregates server-side through SQL pass-through queries.

• Index foreign keys and date columns used in WHERE clauses—every single one.
• Rewrite open-ended LIKE "*text*" queries into anchored matches or full-text equivalents on the server.
• Use pass-through queries for any report that scans more than a few thousand rows.
• Retire subdatasheets and auto-correct rules that drag the catalog on every open.

VBA compilation

Compile, lock, and snapshot

Every production FE must be compiled (Debug → Compile) and distributed as an .accde. Unsigned, uncompiled projects are one bad reference away from a morning of support calls. For read-only workloads over WAN, DAO snapshots avoid row-level locking and reduce round trips—especially useful for dashboards and widget panels that do not need to see live edits mid-render.

' basFastRead.bas — read-only DAO snapshot for fast dashboards over VPN/WAN.
' Snapshots do not honor row-level changes made after open: use for reports,
' not for bound edit forms.
Option Compare Database
Option Explicit

Public Function GetDashboardSnapshot(ByVal sqlText As String) As DAO.Recordset
    On Error GoTo Fail

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

    Set db = CurrentDb
    ' dbOpenSnapshot = read-only, no row-level locking, fewer round trips.
    ' dbSeeChanges only relevant for ODBC-linked tables with IDENTITY columns.
    Set rs = db.OpenRecordset(sqlText, dbOpenSnapshot, dbReadOnly)

    Set GetDashboardSnapshot = rs
    Exit Function
Fail:
    ' Log Err.Number, Err.Description — never swallow errors silently.
    Set GetDashboardSnapshot = Nothing
End Function

' Usage:
' Dim rs As DAO.Recordset
' Set rs = GetDashboardSnapshot("SELECT SalesID, Region, Total FROM vwSalesDaily")
' Me.lstDashboard.Recordset = rs

Use snapshots for reports and read-only grids. For editing surfaces, prefer bound dynasets with bounded scope—not wide unfiltered recordsets.

SQL Server upsizing

When the back-end needs a grown-up tier

When concurrent writers, file size, or reporting demands exceed honest Access territory, upsize the back-end to SQL Server—on-prem or Azure—and keep Access as the UI. This is modernization, not retirement. Read MS Access vs SQL Server: when is it time to migrate and migrate Access database to SQL Server for the staged path.

Network optimisation for UK & Canada teams

SMB/CIFS lag, distributed branches, and VPN design

For UK and Canada teams running distributed branches, the network itself is the performance boundary. Plan VPN profiles and share placement against real user topology; do not chase microseconds in VBA when the packet path is the bottleneck. These are core Access database performance best practices UK and MS Access performance tuning Canada patterns we apply in production engagements.

• Colocate back-ends with the site that writes most; avoid transatlantic writes on hot tables.
• Prefer RDP session hosts beside the BE for remote writers instead of naked SMB over WAN.
• Verify SMB 3.x + multichannel; retire any CIFS / SMB 1 remnants in the path.
• Instrument and baseline before and after each change—no fashion-driven tuning.

Enterprise stabilization

Performance as a compliance concern

Stabilization is a disciplined program: measured baselines, indexed query plans, compiled VBA, managed FE deployment, and honest SQL upsizing decisions. That is what produces the kind of outcomes leadership signs off on—not a weekend of Compact & Repair.

Baselines are illustrative composites. Your numbers should be measured in your environment; we document methodology with each engagement.