Expert guide

DLookup Slow in Access? Faster Alternatives That Work

One query instead of thousands: joins, totals subqueries and cached lookups

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: DLookup, DSum and DCount each run a separate query, so using them per row in a query or continuous form multiplies the work by the number of rows. Replace them with a join to the lookup table, a join to a grouped totals subquery for aggregates, a single recordset in VBA, or a Dictionary loaded once. Index the fields used in criteria. This kind of rewrite is where most of the gains come from in our Access optimisation projects.

Domain functions are convenient, which is why slow Access databases are full of them: a customer name looked up with DLookup in every row of an orders query, an outstanding balance calculated with DSum in a continuous form, a count of open jobs with DCount in a report detail section. Each looks harmless on ten rows. On ten thousand rows over a network, the form takes a minute to open.

Where Domain Functions Hurt, and Where They Are Fine

UseRunsVerdict
Single form control showing one valueOnce per record shownFine
Reading a setting in VBAOnceFine
Query columnOnce per row returnedReplace
Continuous form or datasheet controlOnce per visible row, again on scroll and refreshReplace
Report detail sectionOnce per row printedReplace
VBA loopOnce per iterationReplace

Replace DLookup in Queries With a Join

Slow:
SELECT o.OrderID, o.OrderDate,
       DLookup("CompanyName", "tblCustomers", "CustomerID=" & o.CustomerID) AS Company
FROM tblOrders AS o;

Fast:
SELECT o.OrderID, o.OrderDate, c.CompanyName AS Company
FROM tblOrders AS o
LEFT JOIN tblCustomers AS c ON o.CustomerID = c.CustomerID;

A LEFT JOIN returns every order even when the customer is missing, matching DLookup's behaviour of returning Null.

Replace DSum and DCount With a Totals Subquery

Slow:
SELECT c.CustomerID, c.CompanyName,
       Nz(DSum("Amount", "tblInvoices", "CustomerID=" & c.CustomerID & " AND Paid=False"), 0) AS OpenBalance,
       DCount("*", "tblInvoices", "CustomerID=" & c.CustomerID) AS InvoiceCount
FROM tblCustomers AS c;

Fast:
SELECT c.CustomerID, c.CompanyName,
       Nz(t.OpenBalance, 0) AS OpenBalance, Nz(t.InvoiceCount, 0) AS InvoiceCount
FROM tblCustomers AS c
LEFT JOIN (
    SELECT CustomerID,
           Sum(IIf(Paid = False, Amount, 0)) AS OpenBalance,
           Count(*) AS InvoiceCount
    FROM tblInvoices
    GROUP BY CustomerID
) AS t ON c.CustomerID = t.CustomerID;

The grouped subquery reads the invoices table once and produces one row per customer. Save it as its own query (qryCustomerInvoiceTotals) if you use it in several places.

Continuous Forms

  • Base the form on a query that already joins the lookup values and totals, instead of DLookup or DSum control sources.

  • Totals across all rows belong in the form footer with =Sum([Amount]), which uses the form's own recordset.

  • If a value must be calculated per row and is expensive, compute it once into a field with an update query on a schedule, rather than live.

In VBA: One Recordset, Not Many Lookups

Slow (one query per row):
Do While Not rs.EOF
    rate = DLookup("Rate", "tblRates", "ProductID=" & rs!ProductID)
    ...
Loop

Fast (load once into a Dictionary):
Dim rates As Object, r As DAO.Recordset
Set rates = CreateObject("Scripting.Dictionary")
Set r = CurrentDb.OpenRecordset("SELECT ProductID, Rate FROM tblRates", dbOpenSnapshot)
Do While Not r.EOF
    rates(CStr(r!ProductID)) = r!Rate
    r.MoveNext
Loop
r.Close

Do While Not rs.EOF
    rate = rates(CStr(rs!ProductID))
    ...
Loop

Better still, express the whole operation as one UPDATE query with a join, which lets the database engine do the work in a single pass.

Indexes Matter Either Way

  • Index foreign keys (CustomerID in tblOrders, OrderID in tblOrderLines). Access does not do this automatically for every relationship you create in older files.

  • Index fields used in WHERE clauses and criteria of remaining domain functions.

  • Avoid functions on indexed fields in criteria (Year([OrderDate])=2026); use ranges (>= #2026-01-01# And < #2027-01-01#) so the index can be used.

Linked SQL Server Tables

Domain functions against SQL Server linked tables are especially slow, because each one is a round trip to the server. Rewriting queries as joins helps; moving heavy aggregation into SQL Server views helps more. See AI-assisted Access to SQL Server migration for translating these queries, and query too complex for related rewrites.

Measure Before and After

Dim t As Single: t = Timer
DoCmd.OpenForm "frmOrders"
Debug.Print "Opened in "; Format(Timer - t, "0.00"); " seconds"

Time the slowest forms and reports before changing anything, then after each rewrite. It is common to see multi-second loads fall well under a second once domain functions are gone, especially over a network.

FAQ

DLookup performance FAQ

Domain functions and faster alternatives

Each DLookup call runs a separate query. In a form control that is fine; in a query column or a continuous form it runs once per row, so 5,000 rows means 5,000 queries. Over a network or against linked SQL Server tables, that becomes very slow.

Join the lookup table in the query. A LEFT JOIN returns the same value as DLookup for every row in a single pass. For aggregates such as DSum or DCount per row, join to a grouped (totals) subquery instead.

Not for occasional single values, such as reading a setting. In loops, avoid it: open one recordset with all the values you need, or load them into a Dictionary once and look them up in memory.

Yes. A DLookup whose criteria use an indexed field is much faster than one that scans the table. Index the fields used in criteria, but the real gains come from replacing per-row domain functions with joins.

Yes. All domain aggregate functions (DLookup, DCount, DSum, DAvg, DMax, DMin, DFirst, DLast) run their own query each time. The same alternatives apply: joins, totals subqueries, and recordsets in code.