Turn Excel into a powerful information systems development platform * Enjoy the power of a relational Database in your Excel VBA applications * Business applications like you never thought possible with Excel * Today we write SQL queries from Excel VBA to MySQL DB
Be prepared!
Last week, I guided you through setting up MySQL server and Excel so that they can communicate with one another.
If you don’t have a ready environment for Excel VBA to talk with MySQL server – complete this first and jump back afterwards.
As I want to focus on writing SQL queries from VBA, I will assume you already have some knowledge about programming with VBA, more or less equivalent to what I teach in my entry course online: Computer Programming with Excel VBA.
The anatomy of communicating with a Database server
The Database server should always be running. During installation, it is customary to configure the DB service to start with the server machine itself (your Laptop Windows, for that matter), therefore it should be “on” and listening for requests.
A typical session between a client (Excel in our case) and a DB server has 3 steps:
- Establish a connection
- Submit requests from the client and receive the response coming back from the server
- Terminate the connection
The connection step handles authentication and establishing the session channel.
The request/response step may entail as many requests as you want.
The termination step closes the session and logs out the client.
Connecting to the DB Server
I prepared two VBA Subs to handle connection and termination.
A public variable I call ConnDB holds the session.
The ConnectDB Sub instantiates ConnDB.
Any other Function or Sub can use ConnDB to submit requests to the DB.
The DisconnectDB Sub terminates ConnDB.
So, ConnDB is our main vehicle for communicating with the DB server!
Here’s my ConnectDB Sub I use in all my business applications. Note the declaration of ConnDB is outside of the Sub, as it should be publicly accessible from any Function in any module in the project.
I am picking up all of the connection info needed from cells I maintain in a special “settings” Worksheet, but you can of course provision them as you wish.
The ODBC Driver should match the name of the ODBC Client you installed last week. For me it is currently: MySQL ODBC 8.0 Unicode Driver.
The Server_Name is the Host name (machine name) on which the MySQL Server is installed. If it is installed on the same machine as the client (Excel) – you can just set it to be localhost.
The default port for MySQL is 3306 unless you changed it, and the Database_Name is the Schema (or Database) name you created in MySQL server. I usually create the schema and all tables also from Excel VBA upon the first run of the application, but you can use MySQL Workbench for easy creation of your schema.
Passing the schema name is not mandatory, but if you do, the schema is set as the default throughout the session so you need not explicitly include it in your queries.
ConnDB is instantiating the ADODB service we referenced from our project when we setup Excel to communicate with MySQL server last week.
Option Explicit
Public ConnDB As ADODB.Connection
Sub ConnectDB()
'Open the connection to the DB if not already open
Dim Password As String
Dim Server_Name As String
Dim port As String
Dim User_ID As String
Dim Database_Name As String
Dim connOptions As String
Dim ODBCDriver As String
On Error GoTo FailedConnection
With ThisWorkbook
ODBCDriver = .Names("ODBC_Driver").RefersToRange.Value
Server_Name = .Names("Server_Name").RefersToRange.Value
Database_Name = .Names("Database_Name").RefersToRange.Value
User_ID = .Names("User_ID").RefersToRange.Value
Password = .Names("Password").RefersToRange.Value
port = .Names("Port").RefersToRange.Value
connOptions = ";OPTION=16427" 'Convert LongLong to Int
End With
If (ConnDB Is Nothing) Then
Set ConnDB = New ADODB.Connection
End If
If Not (ConnDB.State = 1) Then
ConnDB.Open "Driver={" & ODBCDriver & "};Server=" & _
Server_Name & ";Port=" & port & ";Database=" & Database_Name &
";Uid=" & User_ID & ";Pwd=" & Password & connOptions & ";"
End If
Exit Sub
FailedConnection:
MsgBox "Failed connecting to the DB. Please check DB settings.", vbOKOnly + vbCritical, "Database Error"
Set ConnDB = Nothing
End Sub
Terminating the DB connection
The matching DisconnectDB Sub looks like this:
Sub DisconnectDB()
'Close the connection to the DB if open
On Error GoTo FailedConnection
If Not (ConnDB Is Nothing) Then
If Not (ConnDB.State = 0) Then
ConnDB.Close
End If
End If
Exit Sub
FailedConnection:
MsgBox "Failed closing the DB connection.", vbOKOnly + vbCritical, "Database Error"
Set ConnDB = Nothing
End Sub
Submitting SQL Queries from Excel VBA to MySQL Server
Now that we can turn ConnDB “on” and “off”, we can start submitting SQL queries from our program!
By the way, SQL is the standard language used with most Relational Database Systems, so it is worthwhile learning how to use it.
Here’s a sample Function that queries the order_headers table for its most recent 5 orders.
In this example I am using the Recordset object available with the ADODB service for storing and navigating the result-set I’m getting back from the DB server. The Recordset object has some convenient properties and methods for navigating, reading, and manipulating records in a table-like structure.
I’m demonstrating two techniques for managing the returned result-set:
- Assigning the returned records into an array variable. Arrays are highly performant and easy to throw into a Worksheet Range of cells using a single statement – see this Blog post on arrays for more details and code examples.
- Using the Recodset object to loop through all records in the result-set.
Sub GetFiveRecentOrders()
Dim strSQL As String
Dim rs As New ADODB.Recordset
On Error GoTo FailedSub
strSQL = "Select * From order_headers ORDER BY order_date DESC LIMIT 5;"
'Note we need not qualify the schema name, as we specified our schema in the connection string already!
ConnectDB
rs.Open strSQL, ConnDB
Dim arr() As Variant
arr = rs.GetRows
arr = Application.WorksheetFunction.Transpose(arr)
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs!order_number, rs!order_date, rs!order_status
rs.MoveNext
Loop
CloseSub:
If Not (rs Is Nothing) Then
If (rs.State = 1) Then
rs.Close
End If
End If
DisconnectDB
Exit Sub
FailedSub:
MsgBox "Failed reading from the Database.", vbOKOnly + vbCritical, "Database Error"
GoTo CloseSub
End Sub
In the next Function, I am submitting an SQL statement that returns a single value, also known as Scalar queries. These scalar queries are perfect for aggregation queries employing SUM, COUNT, MAX, and the like.
Function GetOrdersSumInYearAndStatus () As Variant
'Returns the total sum of orders in strStatus in intYear
Dim intYear as Integer
Dim strStatus as string
Dim strSQL As String
Dim rs As New ADODB.Recordset
Dim result As Variant
On Error GoTo FailedSub
intYear = 2021
strStatus = “Completed”
strSQL = "SELECT SUM(ORL.total)"
strSQL = strSQL & " FROM order_lines AS ORL"
strSQL = strSQL & " JOIN order_headers AS ORD ON ORD.order_number=ORL.order_number"
strSQL = strSQL & " WHERE ORD.order_status='" & strStatus & "'"
strSQL = strSQL & " AND YEAR(ORD.order_date)=" & intYear
ConnectDB
rs.Open strSQL, ConnDB
If Not rs.EOF Then result = rs(0) 'We return the first element in the rs object
If IsNull(result) Then result = ""
GetOrdersSumInYearAndStatus = Trim(result)
CloseSub:
If Not (rs Is Nothing) Then
If (rs.State = 1) Then
rs.Close
End If
End If
DisconnectDB
Exit Function
FailedSub:
MsgBox "Failed reading scalar value from the Database.", vbOKOnly + vbCritical, "Database Error"
GoTo CloseSub
End Function
Writing Mission-Critical Business Applications with Excel VBA
I trust you can now appreciate the potency of a well-programmed VBA application with SQL and an external RDBMS such as MySQL.
Mind you, in many respects, this is no different than writing client-server applications using any other IDE or language, such as MS Visual Studio with .NET, Java, C# or else.
For that reason, Excel VBA is a viable choice for developing highly robust and reliable business application serving many concurrent users and billions of data records.
I offer 3 online courses that teaches you everything you need to know about Databases, SQL and writing professional business applications with Excel VBA and MySQL with ample explained code samples from real-life projects.
By the end of these courses you will have a respected library of generic VBA functions for most tasks against the DB server!
Start with the first course: Databases and SQL Mastery.
No prior knowledge of Databases or SQL is needed, however, programming with VBA is assumed (start with this course if needed).
At this point, please send this Blog post to your colleagues so that you will have a community to practice VBA and SQL together!
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!
Recent Comments