Follow-Up: Avoiding Context Switching

In a recent article, I wrote about the inefficiencies of frequent context switching in software development:

Writing code is kind of like juggling.  Each related procedure is a ball.  You add balls as you work through implementing the logic of the task at hand.

Writing action queries can also be like juggling. If you are working through a process that requires updates to multiple tables, you will have multiple queries.  Each related query is a bowling pin.

Juggling balls is hard.

Juggling bowling pins is hard.

Juggling balls and bowling pins at the same time is darn near impossible.

When performing a complex data manipulation, I argued, the logic is easier to follow if it lives entirely in VBA and you aren't constantly flipping back and forth between VBA and the query editor.

Reader Question

In the comments of that article, reader Huy Truong asked a perceptive question:

Recently you wrote the following article, https://nolongerset.com/design-patterns-in-access/. I watched the embedded video. Around 57:55, [Karl Donaubauer] shared his approach on using Access as an "invisible" application. It seems that he wrote action queries and used VBA to execute them. Isn't that a little contrary to what you wrote in this article? Thanks!

Here's a screenshot from Karl's presentation:

The action queries in that screenshot are a small subset of a complex data transformation process where every action query has a prefix of qry_BA_###.  The code associated with these queries has ZERO logic related to the data transformation process itself.  Rather, it simply loops through each query in alphabetical order to execute the process:

The Context Doesn't Matter...It's the Switching That's a Problem

I think Karl's approach fits in quite well with my original article about avoiding context switching.

My approach is to do all of the data manipulation in code so that I'm not constantly switching contexts between VBA and the query editor.

Karl's approach is to do all of the data manipulation in queries with a very strict naming convention so that he's not constantly switching contexts between VBA and the query editor.

I prefer VBA because it's more flexible and easier for me to follow. My applications are maintained by software developers, so I think this makes sense.

Karl prefers queries because the logic flow is simpler.  The applications Karl works on are usually maintained by other users, often those without any advanced Access or VBA knowledge.  The query-only approach is simpler to understand for Access power users who may be intimidated by code.

Ultimately, it doesn't matter which context you are working in–VBA or queries–the key is to pick one (for a given process) and stick with it.

Referenced articles

Avoid Context Switching for More Readable Code

Writing code is like juggling. But mixing code, queries, and global state is like juggling balls, bowling pins, and running chainsaws.

No Longer SetMike Wolfe

Image by manseok Kim from Pixabay