Developing Business Applications with Excel VBA and MySQL Server

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:

1. Establish a connection
2. Submit requests from the client and receive the response coming back from the server
3. 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:

1. 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.
2. 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!