When writing a query in Microsoft Access, it's always faster to use native Jet/ACE functions and statements, like
Is Null, rather than calling a custom VBA function. If you are trying to squeeze every last bit of performance out of your queries, then that is the way to go.
But don't get ahead of yourself! Before you start worrying about how fast your query is, you need to make sure it's producing correct results.
Young Me: "Look how fast my query is!"
As a young developer, I was always looking to maximize performance. That meant doing things like avoiding the
Nz() function in favor of Jet's native
Is Null statements.
For example, the following two query fields will return identical results, but the second will perform much better than the first:
Now, swapping out IIf and Is Null for the Nz function is something I still regularly do. What I've stopped doing is nesting IIf statements just to avoid calling into VBA.
Older Me: "Look how correct my query is"
Nested IIf statements are easy to write.
The problem is they are nearly impossible to read.
Let's take a look at another example of two query field definitions that return identical results.
The first uses pure Jet/ACE SQL:
The second performs the same calculation using a VBA function:
CalcAmtDue([DueDate],[OriginalAmt],[DiscountAmt],[PenaltyAmt],[AsOf]) AS CalcDue
There is no question that the second is easier to read. As long as you pass the arguments in the correct order to the function, it's almost impossible to screw it up.
Young Me: "But your function could be wrong!"
Code is always harder to read than it is to write. It doesn't matter if the code is VBA or SQL. So how big an advantage is it really to write the logic in VBA, especially when you know it is going to run slower?
Well, I can think of a few reasons why the logic is more likely to be correct in VBA than in SQL:
- VBA can be commented
- VBA allows defining constants
- VBA preserves whitespace
- The same VBA function can be called from multiple queries
- And, most importantly, the VBA function can be tested – BOOM! [mic drop]
Older Me: "I can prove the VBA is correct"
Here's the accompanying VBA function. Notice that the function is documented and verified with doc tests. With this approach, I can prove that there are no pesky off-by-one errors, etc. If I come up against a weird, bug-producing edge case in the future, I can:
- Document a failing test for the bug
- Refactor the function to pass the test
- Run the other tests to ensure I didn't introduce any regressions
'>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #5/31/2020#) ' 98 '>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #6/1/2020#) ' 100 '>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #6/30/2020#) ' 100 '>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #7/14/2020#) ' 100 '>>> CalcAmtDue(#6/30/2020#, 100, 2, 10, #7/15/2020#) ' 110 Function CalcAmtDue(DueDate As Date, OriginalAmt As Currency, _ DiscountAmt As Currency, PenaltyAmt As Currency, _ Optional CalcAsOf As Date = #12/31/9999#) As Currency Const DiscountDays As Long = 30 Const PenaltyDays As Long = 15 Dim AsOf As Date If CalcAsOf = #12/31/9999# Then AsOf = Date Else AsOf = CalcAsOf End If Select Case True Case (DueDate - AsOf) >= DiscountDays CalcAmtDue = OriginalAmt - DiscountAmt Case (AsOf - DueDate) >= PenaltyDays CalcAmtDue = OriginalAmt + PenaltyAmt Case Else CalcAmtDue = OriginalAmt End Select End Function
Young Me: "Have fun waiting for your report to open, old man!"
There is no question that calling into a VBA function from a query will severely reduce the query's performance. I'm certainly not advocating for ignoring performance just for the sake of readability. But correctness is always more important than speed.
You can fly from New York to Los Angeles in 6 hours, while it takes me 12 hours to drive from New York to Chicago. That's twice as long to travel only half as far. But if the goal is to watch a Cubs-White Sox game, I'm the only one who'll be able to watch the game in-person.
Older Me: "Sounds like someone's having premature optimizations"
For most relatively complex calculations, you won't be performing them against thousands of records. Oftentimes, you can get acceptable performance simply by aggressively filtering your data so that there are fewer calls to the VBA function.
And, if there truly is a large, set-based operation that needs to be optimized, you can always do that later once it's clearly necessary.
In the meantime, you'll have an application that's easier to write, read, and maintain.