Recordset Helper for MS Access – Faster Than DLookup & Co.

Domain functions like DLookup, DSum or DCount are handy – but they hit their limits as your data grows. This compact VBA module replaces them with fast, robust, recordset-based functions and even adds functions that Access does not offer natively.

We’ve all been there: the built-in domain functions like DLookup, DSum or DCount are handy – but when your data grows and gets more complex, they hit their limits. Today I introduce a compact, high-performance VBA module that takes the stress out of this: modern, stable, recordset-based functions – faster, more robust, more flexible and fully traceable. No add-ins, just VBA.

The module – what’s in it?

The module not only replaces the classic domain functions, but also provides functions that Access does not offer natively.

Replacement for the domain functions

For DLookup, DSum, DCount, DMax, DMin, DFirst, DLast (plus TAvg) there are T-prefixed equivalents:

' instead of
DLookup("Price", "tblProducts", "ID=123")

' simply
TLookup("Price", "tblProducts", "ID=123")

Advantages over domain functions:

• no multiple database access per function call
• better performance
• fully controllable SQL
• stable with large amounts of data
• uniform error handling
• easy to debug

New functions that Access does not have

Access comes with many useful building blocks – but not everything. This module expands your toolbox:

Highlight: ThisDB instead of CurrentDb()

CurrentDb() looks harmless – but each call opens a new internal connection.

The solution is a property that holds a static reference to the database object – fast, efficient and reusable:

Public Property Get ThisDB() As DAO.Database

Conclusion

This small module takes your Access development to the next level: faster, more secure, more transparent – completely in VBA. You’ll find the detailed description directly in the code, well documented as usual.

Download