Using an Access, MySQL, PostgreSQL or SQLite Database from VBA

Important note: The conversion of the original blog post to the CodeProject format is not complete.

You can find the original article here.

I'll update the CodeProject article as soon as possible.

Introduction

Note to pedantic guys: Yes Access is not a database engine, only the graphical front-end to Jet/ACE, but we’ll stand with this simplification.

The sample application (Excel + VBA) and the SQL source code are available in this zip archive.

If you are an advanced VBA developer who builds applications that manage a non trivial amount of data, odds are good you are using an Access database to store them.

If this setup perfectly fits your current needs, you completely master it, you’re not experiencing any issue and your needs won’t evolve in the near future, you can skip this article and continue enjoying your system.

Indeed, do you really need a new database management system (DBMS)?
Often the only argument in favor of migrating to other DBMS is they are “better”; while it’s true for some technical capabilities, it may not be regarding other “metrics” like simplicity: Access is easy to understand and manage for non IT staff and is often installed with default business workstation setup along with the rest of the Office suite.

So let’s say you have strong reasons to migrate to a new DBMS because you’ve come to the point where you feel you need support for at least one of the following features: interoperability, big storage, high concurrency (hundreds of users) and/or high performance, and Access starts to become a part of the problem.
So what can you do if you want to enhance the quality of your database without making your total cost of ownership (TCO) explode?

Your TCO is essentially made of:

• Licensing costs: Limiting them is quite simple: using a free, usually open-source, database and paying only for support
• Management costs: They are by far bigger than the licensing costs and are directly impacted by the complexity of the DBMS; so you need a simple DBMS that you’ll be able to setup and manage yourself as you used to do with Access without the help of system or database administrators
• Development costs: Every additional change to your current schema or VBA implementation to fit the new DBMS will have a cost; so we want things to be transparent with zero additional development, which in particular means a standard SQL-based DBMS.

While the equation may seem a little complex, it has at least three solutions:

• SQLite is the ideal candidate if you’re happy with the “single-file” model, you don’t have high concurrency constraints, and your only needs are interoperability (with Mac OS, Linux, Unix…), bigger storage and/or costs savings,
• MySQL and PostgreSQL: If you need support for high-concurrency, really big storage (e.g. tens of GBs), advanced user-management, performance fine tuning and other advanced features, you’ll have to jump out of the single-file world.
  If you don’t have specific requirements, then MySQL and PostgreSQL will appear similar to you and equally do the job. However, in this kind of situation, I have a preference for MySQL, not because its inherent capabilities would be superior (as I’ve said, MySQL and PostgreSQL are roughly equivalent for simple setups), but because, as the reference open-source DBMS for years, MySQL benefits from a huge community and toolbox. Moreover, while you’ll surely find the tools to work in good conditions with PostgreSQL, if you ever need to justify your choice to your hierarchy, you’ll be in a better position if you choose the standard solution instead of the challenger.
  But as I’m not sectarian, and for completeness, I’ll cover both.

In this article, I’ll quickly cover the setup of these three DBMS (with links to other resources for more extensive instructions) and illustrate their usage with a small VBA application, a revolutionary todo-list manager, that uses Access too.

Data Schema

The data schema used by our application is really basic: one table with 3 columns:

• Description: A textual description of the task
• Category: A tag that will help further categorization of the bunch of tasks you’ve created
• Due date: The limit date for accomplishing the task, after you could be in trouble with your boss!

Here is how it looks like in Access:

The equivalent in Access SQL dialect is:

CREATE TABLE Tasks
(
	Description	LONGTEXT,
	Category	VARCHAR(100),
	DueDate		DATETIME
); 

MySQL

Installation

You can download the MSI installer from here: MySQL.

Once downloaded, start it and accept any Windows security popup that could appear.
Then, you can follow this slide-show for further instructions:
[simple_slides set="MySQL_Setup" theme="bar" pause_time="3600000"]
and/or follow this video (directly go to 10:15 if you only want a quick introduction to MySQL Workbench):

Schema

In MySQL SQL dialect, our schema creation query is:

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		DATETIME
); 

Note that the “Description” field is using the limited “VARCHAR” type instead of the unlimited “TEXT” type because I’ve noticed some issue when retrieving this type of data using VBA.
Feel free to increase the maximum number of characters if 1000 seems too little for you (you can go up to more than 65000!).

PostgreSQL

Installation

PostgreSQL can be downloaded from the EnterpriseDB website.

To set it up quickly, you can follow this slide-show:
[simple_slides set="PostgreSQL_Setup" theme="bar" pause_time="3600000"]
If you want more complete instructions along with a quick introduction to using pgAdmin (it starts at 03:40), you can watch this nice video tutorial:

Schema

In PostgreSQL SQL dialect, our schema creation query is:

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		TIMESTAMP
); 

Almost identical to MySQL except the name of the type representing a date and time: TIMESTAMP.
MySQL has a type named TIMESTAMP too but with a different meaning.

SQLite

Installation of SQLite Expert

As Access, an SQLite database does not use a dedicated server to work but is contained into a single file accessed through a standard API.
So contrary to MySQL and PostgreSQL, you only need to setup a management tool.

I know two very good tools:

• SQLite Manager,
• SQLite Expert

They’re both powerful and easy to use, but as SQLite Manager is delivered as a Firefox addon, we’ll prefer the personal version of SQLite Expert (of course, if you’re using it in a professional environment, you should pay for the full version " class="wp-smiley" /> ) as it is self-contained; but feel free to try SQLite Manager if you’re using Firefox, it’ll be even easier to setup.

[simple_slides set="SQLiteExpert_Setup" theme="bar" pause_time="3600000"]

Here is a series of video tutorials from the SQLite Expert site that will show you how to:

• Create a new database:

  http://www.sqliteexpert.com/tutorial_create_db.swf

• Create a new table with the designer:

  http://www.sqliteexpert.com/tutorial_create_table.swf

• Create a new table using SQL (you can execute the SQL script presented above):

  http://www.sqliteexpert.com/tutorial_create_more_tables.swf

Installation of SQLite ODBC Driver

As for MySQL and PostgreSQL, to use your SQLite database, VBA needs an ODBC driver, while for MySQL and PostgreSQL, their respective drivers are installed transparently along with the servers, the SQLite driver is only available as a separate package.

You can download this driver from the SQLite ODBC Driver web-site.

Then to install it, follow the guide:

[simple_slides set="SQLiteODBCDriver_Setup" theme="bar" pause_time="3600000"]

To check the installation, launch the “ODBC Data Source Administrator”.
Go to the “Drivers” tab and look for the SQLite driver entry:

Schema

In SQLite SQL dialect, our schema creation query is:

CREATE TABLE Tasks
(
	Description	VARCHAR(1000),
	Category	VARCHAR(100),
	DueDate		TIMESTAMP
);

This is the exact same SQL code as for MySQL.

The Application

So, from here, I assume you have correctly setup one or more DBMS, and that you are able to interact with them using their dedicated management application (MySQL Workbench, pgAdmin and SQLite Expert respectively).
Here comes the interesting part where we’ll use these DBMS to store and retrieve our data from an Excel/VBA application.

The User Interface

The application is a basic task management system with a unique sheet that allows the user to see his current list of tasks and to update it before eventually saving it to the database.
Here is a screenshot of the application with my current todo-list:

There are four inputs (respectively two basic cells and two ActiveX TextBox with property PasswordChar set to “*“):

• The source: what is the type of DBMS used; this will determine the information we need to provide and in which format for the connection string
• The location: where to find the database; for MySQL and PostgreSQL that are server-based, it is the address of their host (if this is the same machine, you’re using to run the Excel application use “localhost“), for Access and SQLite that are file-based it is the path of the database file (the sample is able to interpret path relative to the current workbook location)
• The user name, and
• The user password: if you’ve setup some user-based security, you’ll need to provide them to interact with the database; note that the sample only takes them into account for server-based DBMS (MySQL and PostgreSQL)

Here are samples of configuration for each database:

Access	MySQL	PostgreSQL	SQLite

For MySQL and PostgreSQL, the login/password are the one you used to connect with MySQL Workbench and pgAdmin respectively.

Using the Databases from VBA

The good news is that whatever the DBMS you’ve chosen, you’ll use a common procedure to interact with it.
First, you need to know which driver you’ll use to ensure communication between your VBA code and your DBMS. The role of the driver is to implement a standard API for a particular DBMS; this way, whatever the underlying DBMS, the code using it can communicate with it in a unified manner which among other advantages allows you to switch between different DBMS as your needs evolve.

For MySQL, PostgreSQL and SQLite, I’ve used an ODBC driver, but not for Access as its ODBC driver is more limited and, among other things, does not support transactions which was a feature I wanted to use in the application for a cleaner update.

There is only one thing that will differ when using different DBMS: the connection-string, but once the connection is established, all the other manipulations (retrieval and updates of data) are handled in a common way which avoid having to write one code by DBMS.

In the VBA code, the construction of the correct connection-string for and connection to a given source is managed by the “OpenConnection” method:

Private Function OpenConnection() As ADODB.connection
    ‘ Read type and location of the database, user login and password
    Dim source As String, location As String, user As String, password As String
    source = Range("Source").Value
    location = Range("Location").Value
    user = TasksSheet.UserInput.Value
    password = TasksSheet.PasswordInput.Value
    ‘ Handle relative path for the location of Access and SQLite database files
    If (source = "Access" Or source = "SQLite") And Not location Like "?:\*" Then
        location = ActiveWorkbook.Path & "\" & location
    End If
    ‘ Build the connection string depending on the source
    Dim connectionString As String
    Select Case source
        Case "Access"
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & location
        Case "MySQL"
            connectionString = "Driver={MySQL ODBC 5.2a Driver};_
            Server=" & location & ";Database=test;UID=" & user & ";PWD=" & password
        Case "PostgreSQL"
            connectionString = "Driver={PostgreSQL ANSI};_
            Server=" & location & ";Database=test;UID=" & user & ";PWD=" & password
        Case "SQLite"
            connectionString = "Driver={SQLite3 ODBC Driver};Database=" & location
    End Select
    ‘ Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    Call OpenConnection.Open(connectionString)
End Function 

This is the only part of the code that is not generic with regards to the underlying DBMS.
You could use it in any project potentially involving more than one source of data to uncouple the rest of the code from the trouble of building the connection-string and establishing the connection.

The Remaining Source Code

The rest of the source code, in the module “mTasks“, contains the handlers for the click events on the buttons:

Public Sub LoadTasksButton_Click()
    Dim output As Range
    Set output = Range(TopLeft).Offset(1).Resize(1000, 3)
    output.ClearContents
    Dim connection As connection
    Set connection = OpenConnection()
    Dim result As ADODB.Recordset
    ‘ Load all the tasks from the database
    Set result = connection.Execute("SELECT description,category,dueDate FROM tasks")
    ‘ Insert them into the dedicated area
    Call output.CopyFromRecordset(result)
    connection.Close
End Sub
Public Sub UpdateTasksButton_Click()
    Dim connection As connection
    Set connection = OpenConnection()
    ‘ Create a record-set that holds all the tasks
    Dim records As ADODB.Recordset
    Set records = New ADODB.Recordset
    Call records.Open("SELECT description,_
    category,dueDate FROM tasks", connection, , adLockOptimistic)
    ‘ Begin a transaction to avoid corrupting the database in case of error
    connection.BeginTrans
    ‘ Clean up the tasks list
    While Not records.EOF
        records.Delete
        records.MoveNext
    Wend
    ‘ If there is at least one task
    If Not IsEmpty(Range(TopLeft).Offset(1)) Then
        Dim dataRange As Range
        Set dataRange = Range(TopLeft).Offset(1).Resize(, 3)
        ‘ If there is more than one task
        If Not IsEmpty(Range(TopLeft).Offset(2)) Then
            Set dataRange = Range(Range(TopLeft).Offset(1), _
            Range(TopLeft).Offset(1).End(xlDown)).Resize(, 3)
        End If
        Dim data As Variant
        data = dataRange.Value2
        Dim i As Integer
        For i = 1 To UBound(data)
            Dim row As Variant
            ‘ Extract ith row
            row = Application.WorksheetFunction.Index(data, i, 0)
            ‘ Add a new task
            records.AddNew
            records("Description") = row(1)
            records("Category") = row(2)
            records("DueDate") = CDate(row(3))
        Next i
    End If 
    records.Save
    records.Close
    ‘ Commit all the work: cleanup + new tasks
    connection.CommitTrans
    connection.Close
End Sub 

As you can see, there is nothing specific to a given DBMS, just generic data plumbing code.
You don’t need (and don’t want ) to know which DBMS you’re using, you just need to know it is compliant with some standards like SQL to interact with it.

Conclusion

As you’ve seen, using a different DBMS than Access for your storage layer is a straightforward process that, if correctly implemented, won’t cost you a lot up front and could greatly enhance your applications and then your business if you fully leverage them to run it.

Later, if your database really becomes a critical asset for your business, holding tons of important data, with hundreds of requests per minute, high availability requirements, and that any shortage could represent a true operational risk, you may want to (and really should) let a dedicated database-administrator (DBA) manage it, optimize it, and take care of all the tricky stuff.

Depending on your IT organization, you could lose a lot in terms of flexibility, e.g., when you need to change a table structure or create a new one, but this is often the cost to pay for enhanced reliability and performance.

If you catch any typo or mistake, encounter any issue or have additional questions feel free to let a comment, I’ll do my best to answer in a timely manner.

Thanks for reading!