In case you miss the outstanding promise of that title up here, you are about to discover a new world of capabilities and opportunities you have never imagined with Excel.
Most of the business application running our world’s economy (all organizations out there) – rely on a strong relational Database as the backbone of its information systems.
These Databases handle trillions of records, serving operational and analytical transactions and queries in a high speed, reliability, and flexibility.
It is only in the past 15 years or so that a new Database paradigm was introduced to handle today’s unique scale and social uses of applications of our time: Graph Database (I may write a Blog post about these new players one day).
Once you can program with VBA and work with an external Relational Database Management System (RDBMS) for your persistent data requirements – you can develop almost any business application you want.
I know some consultants do not approve Excel to be chosen as a business application platform to begin with, but I disagree with the belief that no business application can or should be developed with Excel and an external RDBMS.
In fact, I have been developing and delivering mission-critical business application for many years to customers around the world, mostly with Excel and MySQL RDBMS.
I am talking about systems to manage production planning and execution, human resources and training, banking (mortgage and investments products), sales management, Reinsurance brokerage and many others. These are highly cost-effective and reliable. Once I finish installation and initial guidance – the only reason for the customer to call back is for more solutions – not complaints.
So, the proof is in the pudding, or in other words, in solid and exceptionally reliable applications that run for years, in which the customer invested $20,000 instead of $40,000 had he approached a traditional software house for that application.
And yes, I develop using other technologies too, not only Excel VBA.
Back in June of last year I published a Blog post about writing SQL queries over Excel tables. You should be familiar with that Blog post before reading on.
But even before that, if you feel you need to better understand what a relational Database is – go ahead and read my Blog post titled Understanding Relational Databases - The Basics.
If we want to work with an RDBMS, we need to have access to one.
I like MySQL and use it a lot but note that any standard RDBMS product out there will work just as well.
MySQL Community Edition is a free edition of the complete Database server and additional helpful tools that you can install on any laptop. It doesn’t have to be the same machine you are running Excel on, as long as you have access over a network from your Excel machine to the DB server machine.
Follow the MySQL instructions here to complete this step. I advise to install the complete package, at least install the MySQL Workbench and the MySQL Command Line Client in addition to the MySQL Server component.
Once you have MySQL Server installed and accessible, go ahead and define a user (in addition to the “root” user that is like the “admin” user in other products). Grant enough administrative roles (permissions) to CREATE and SELECT. Selecting the DBManager administrative role will serve you perfectly.
The MySQL Workbench is a Windows graphic user interface to work against the DB Server – use it for easy and intuitive experience.
As you can imagine, MySQL Server and MySQL Workbench are huge sophisticated and capable software products. While this post does not intend to cover MySQL usage, you will find plenty of tutorials for any popular task out there.
A server, unsurprisingly, is designed to serve many clients.
The concept is that clients submit requests to the server. The server caters for the requests and sends a response to the clients. This is known as a client-server architecture.
The MySQL Workbench you installed (and possibly used) is a client communicating with the MySQL Server. Same goes to the MySQL Command Line Client.
We want Excel to act as yet another client.
Clients communicate with servers over an agreed upon protocol. The protocol is the “language” they both understand and facilitates the effective communications between them.
Since Excel does not know the MySQL protocol, we need a middle-man translator to facilitate the communications between them.
The MySQL ODBC Connector is what we’re after and if you hadn’t installed it with the complete package earlier, go ahead and install it from here. Select the 32-bit Windows version even if your Windows is 64-bit. The ODBC Connector is to be installed on the client machine, that is, the machine you run your Excel on (regardless of where the MySQL Server is installed).
ODBC (Open Database Connectivity) is a standard application programming interface for accessing database management systems.
The MySQL ODBC Connector you installed “speaks” with the MySQL server in its proprietary protocol on one end, and “speaks” the standard ODBC protocol on the other end, in effect serving as our translator middle-man.
The last link in the chain we need to do is to bind an ODBC service to our VBA application, so that we can start creating objects and calling their methods to submit requests to the MySQL Server through the ODBC Connector in our VBA program.
This service is provided to us by Microsoft and it is called ADODB.
Let’s do an early binding to the ADODB service by referencing it in our VBA program. While in the VBA editor, select Tools -> reference in the upper menu and tick the Microsoft ActiveX Data Objects 6.1 Library (take the highest version you find if 6.1 isn’t listed).
If you want to better understand early binding vs. late binding – I have just the Blog post for you here!
That’s it! You now have a great setting for Excel to MySQL Server connectivity and you are now ready to start coding your first business application with MySQL. and this is demonstrated in part 2 of this Blog series here - click to continue.
Be sure to share this post with your friends and colleagues far and wide.
Join today to the Excel VBA Inner Circle with Mor Sagmon.Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.