A podcast by Hiran de Silva. Read by Andrew.
When I talk about Excel and Access, I’m discussing a client-server architecture that might differ from what others have in mind. It’s a powerful combination that has proven invaluable in many of the projects I’ve worked on over the years.
This discussion is inspired by Alastair Harris’s post, where Stephen Donaldson and I explored the ongoing relevance of Microsoft Access. It’s a question I encounter often in my professional life, given my 25 years of experience in this space.
From my perspective, Access has played a pivotal role in numerous scenarios throughout my career. There were countless times when business departments, particularly accounts, lacked access to robust databases because IT departments typically handled these systems. Businesses were not expected to possess the technical know-how required for database management, so Access often became the solution.
In one memorable instance, a member of the management team, mistakenly believing Access was not available, was surprised to discover that we could easily create an Access database by simply right-clicking on the desktop. Once I demonstrated what we could achieve by extending the capabilities of Excel with with Access, the solution rapidly gained traction. We used Access as a backend database, with Excel as the frontend interface where 100s of staff across the company could collaborate in the same space. The process looked just like the usual Excel-driven workflow, but behind the scenes, it was vastly more efficient and streamlined. Specifically, no spreadsheets needed to be sent or received anymore – just the data flowed.
Eventually, our success caught the attention of top management, who ordered IT to provide us with a SQL Server. However, note that in the 21st century, the landscape has since changed. Today, instead of waiting on IT, my sponsors can quickly provision a SQL Server through Microsoft Azure in just minutes.
Despite these advancements, Access still holds value, particularly in proof-of-concept scenarios and as a working model. Access, placed on a shared folder where multiple spreadsheets connect, can support complex workflows involving hundreds of spreadsheets without users even realizing there’s a database at the backend. The key is that the user experience remains seamless, and once the solution is proven, transitioning to SQL Server is straightforward–it’s simply a matter of redirecting the connection to the new server.
Reflecting back to the 1990s, Excel was widely used, albeit with limitations. One of the major challenges was the difficulty in sharing spreadsheets–only one person could update at a time, leading to frustrating and error-prone processes. Back then, the common thinking was to “upgrade to Access,” which usually meant bringing in an Access developer to create a more robust system.
A point to note, Access worked well for small teams, such as a publication arm of an exam provider I once worked with, where a department of six people could finally manage their projects efficiently using an Access database – where previously they were struggling with sharing spreadsheets. As an aside, this is something Microsoft tried to solve with Shared Workbooks as from Excel 97. But back to Access, the file server architecture of Access was not scalable for larger teams. But by then, I had discovered how to use ADO to link Excel as the frontend with Access as the backend in a client-server architecture, removing practical limits and supporting hundreds of users with one Access database at the back end.
While Access had a 2GB limit, this was rarely an issue, as the amount of data typically managed was far below this threshold. Over time, many of my Access solutions progressed to SQL Server, especially as I became a Microsoft partner in 1997. Microsoft provided extensive resources, including software through their MSDN subscription and quarterly presentations in London, which were invaluable for staying updated.
Even today, I still use Access for teaching purposes. It’s an excellent tool for demonstrating client-server architecture with Excel before moving on to more robust systems like SQL Server. However, when I refer to “using Access,” I’m typically not talking about the full suite of Access features like forms, Modules or VBA. Rather, I’m using it to create tables and queries (or views, as they’re known in SQL Server) that can be queried from Excel. It’s a simple, user-friendly way to manage data without overwhelming users with technical details.
Recently, I used Access as a backend for a local solution to the Wynn Hopkins and Oz du Soleil Expert Excel Challenge. It was an excellent demonstration of how Access can be effectively used in a proof-of-concept. Then, we scaled the solution to a global level, we transitioned from Access to SQL Server, illustrating how easily these methodologies can evolve.
In conclusion, remember, when I talk about Excel and Access, I’m discussing a client-server architecture that might differ from what others have in mind. It’s based on the synergy of Excel and Access, made possible by ADO or ActiveX Data Objects. It’s a powerful combination that has proven invaluable in many of the projects I’ve worked on over the years.
If you’re interested in exploring this further, feel free to join one of our live Excel happenings, where we delve into these solutions in more detail.
Add comment