In this article, I’m addressing a critical misconception about a common yet powerful Excel solution: the client-server, hub-and-spoke model, specifically for the reg call handler. This approach, as Tony Wong and others have pointed out, is incredibly effective. Yet, some argue it’s “too technical” for typical users, including management, to grasp. My goal here is to debunk this myth by demonstrating just how simple and robust this architecture is. In fact, it’s often the management that recognizes its elegance and functionality after a brief demonstration–just as I’ve seen with various clients across different organizations, from Lloyds Banking Group to WSP’s KPI monthly reporting system. The response from decision-makers has never been that it’s “too advanced”; rather, they appreciate its power and accessibility.
Demystifying the Database Creation Process.
First, let’s dispel the notion that creating a database requires specialized IT support. With Microsoft Office products, especially if you’re accustomed to Excel, setting up a database is as straightforward as creating a new Word document or PowerPoint. You simply right-click, select “New,” choose “Access Database,” and in seconds, you have a functioning database.
Building tables within the database is just as simple. Think of it as setting up an Excel worksheet with headers, but here, each column is designated for a specific type of data (e.g., dates, true/false values), which only helps ensure data integrity. There’s no complexity involved–just logical structure. Once the table is set up, all that remains is to note the database’s location for reference when connecting your spreadsheets.
Enabling Excel’s Data Access Capabilities.
Next, we move to the Excel spreadsheet itself. Excel comes pre-equipped to upload data directly from the sheet into a database. Imagine you have a range of data on the sheet that matches the database table. To upload it, we need to activate a component in Excel: Microsoft ActiveX Data Objects, or ADO, version 6.1.
Activating this component is a matter of enabling a reference in Excel, which takes a few seconds. Once enabled, this unlocks the ability to communicate with databases from within Excel via a few lines of VBA code. This code only needs to specify the database location and tell Excel what action to perform (e.g., inserting rows into the table). You can even set up a simple loop to insert rows automatically, so Excel fills the database row by row.
Is this complex? Hardly. The code required is minimal, generic, and, in my training courses, I provide this code block, which has been tried and tested for years. Even beginners can copy and paste this code to enable data transfers in Excel.
Creating Simple User Controls with Buttons.
To simplify further, you can add a button in Excel labeled “Put.” By assigning the macro to this button, you make it easy for any user to upload data with a single click. Likewise, a “Get” button can retrieve data from the database back to the spreadsheet. With these two buttons, users can control data flow–adding, updating, or retrieving information–without touching the code.
If you need more refined functionality, such as replacing data instead of appending, you simply add a delete action before the insert. Or, if you want filtered results (e.g., selecting records for a specific band like “The Rolling Stones”), you can introduce a dropdown with filter options. These are small refinements, but the core functionality remains intuitive and accessible.
Addressing Concerns About Technical Complexity.
When people label this process as “too technical,” the question arises: Are they speaking for themselves, or are they underestimating the capacity of typical Excel users? Often, trainers or consultants who lack familiarity with databases may mistakenly project their own discomfort onto others. But having seen this process, is there really anything daunting here?
And if you need your database in the cloud for global access, setting it up on Microsoft Azure is also straightforward. Within minutes, you can create a cloud-hosted SQL database, update your Excel connection string, and gain remote access. It’s merely a matter of changing the database path to the cloud location, all using standard Microsoft infrastructure.
Revisiting the History: Excel’s Built-In Data Access.
The capability of Excel to interact with databases is not new. I first discovered it in 1997, though it was originally demonstrated by Satya Nadella in 1993 on the Microsoft DevCast when he was a young engineer at Microsoft. Data access from Microsoft Office products, especially Excel, was heavily promoted throughout the late 1990s and early 2000s. In fact, Microsoft’s message at the time was about separating data from spreadsheets, enabling dynamic, end-to-end solutions. Bill Gates’s book *Business at the Speed of Thought* captured this era’s philosophy, describing a “digital nervous system” where centralized data drives downstream actions.
In practical terms, this means when an order is entered into a spreadsheet, the data automatically updates in the central database, prompting actions like dispatch or invoicing. The architecture, now popularly referred to as the “hub-and-spoke model,” leverages central data to drive connected processes. This terminology, suggested by my colleague Ed Cruz, is a visual and functional way to describe an architecture that supports agile and responsive data flows across departments in a collaborative environment.
Breaking the Myths Surrounding “Ordinary” Users and Databases.
For those who argue this approach is too complex for “citizen developers” – as Mark Proctor terms them, this is simply untrue. The simplicity of ticking a box in Excel and adding a few lines of code is not beyond any typical Excel user’s capabilities. Certainly no more complex than M-code or Office Scripts. Creating a database is no more complex than setting up a PowerPoint deck. Historically, the Microsoft Office Specialist certification even required a knowledge of these data capabilities to gain certification at the expert level.
While certifications may have evolved, the capabilities have not. For decades, Excel has had built-in tools for connecting with databases, whether it was MS Query in the early days or Power Query now. These tools are part of Excel’s DNA, designed for accessibility.
Final Thoughts.
This isn’t rocket science; it’s sound architecture that has been around for years. In dismissing it as “too advanced,” those resistant to change overlook the elegance and practicality of a solution that empowers users to leverage data in real-time. With Microsoft’s legacy of accessible data tools, what I’ve shown here is achievable by anyone willing to understand Excel’s capabilities–capabilities that transform a single-user experience into a cohesive, enterprise-grade data solution.
This is a podcast by Hiran de Silva. Narrated by Bill.
Add comment