Developers Recordset Helpers – Your little recordset toolbox for MS Access!
We've all been there: The built-in domain functions like DLookup, DSum, DCount, and others are handy – but when your data grows and gets more complex, they hit their limits. 🐌
👉 Today, I'm introducing a compact, high-performance VBA helper module that takes the stress out of this situation with modern, stable recordset-based functions that are:
✅ faster
✅ more robust
✅ more flexible
✅ and fully traceable.
No add-ins, no external stuff – just VBA. Perfect for any professional Access development!
🔧 The Recordset Helper 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 DLookup, DSum, DCount, DSum, DMax, DMin, DFirst, DLast, TAvg.
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 in a meaningful way:
Function | Description |
TList | Returns a comma-separated list (e.g., all city names in one line). |
TDistinct | Returns all unique values of a field as a collection. |
TGroup | Flexible group function (SUM, AVG, MAX, MIN, COUNT – controllable via Enum) as a dictionary |
TExists | Returns TRUE if a value exists. |
🧠 Highlight: ThisDB instead of CurrentDb()
CurrentDb() looks harmless – but: Each call opens a new internal connection. With many calls, this leads to:
❌ Performance problems
❌ Error message “Too many databases open” (error 3048)
Solution:
Public Property Get ThisDB(...) As DAO.Database
This property holds a static reference to the database object – fast, efficient, and reusable.
📚 Conclusion
This small module takes your Access development to the next level:
✅ Faster
✅ More secure
✅ More transparent
✅ Completely in VBA
Do you develop a lot with Access? Then give it a try – I look forward to your feedback or ideas for further features! 🚀
You will find the detailed description and all instructions directly in the code - well documented as usual.
DOWNLOAD
Version: 1.0 7th July 2025
A small donation