Database

Introduction: Change the Way You Program in MsAccess (Forever)

Bring the tools of modern languages to VBA: think of tables as collections of dictionaries.

The Database module is used for simplify the production of SQL queries and the processing of results.

It creates an abstraction level where:

1. A table, or query result, is an object (list of dictionaries) that can be easily processed as a whole: no more of that ugly Set rst = OpenRecordset(strSQL)
2. You will have string interpolation tools to write filters and queries in a simple and intuitive manner. No more of cryptic constructs such as foo = " & 5 & " AND bar = '" & "hello"
3. Actually you will often not need to write SQL queries such as SELECT or UPDATE by hand anymore, because you will have some abstract functions that do it for you, safely and quickly.
4. You can treat a remote database -- Oracle, Mysql, etc. -- with the same ease as a local database. Special things like date literals in a query will be automatically converted.

Syntax Helpers

Interpolating SQL strings

One of the annoyances of vba, is that its syntax is not well suited for creating SQL queries. The following type of code is all too common:

strSQL = "SELECT * FROM mytable WHERE foo = " & 5 & " AND bar = '" & "hello" % "'"

Ugh! This is conducive to syntax errors, eyestrain and even combat fatigue. How did the creators of Visual Basic for Applications let us live for years with that monstrosity?

Unfortunately we will not have the secure argument-passing mechanisms that Python provides. But wouldn't it be great, if we could at least write something like:

strSQL = "SELECT * FROM mytable WHERE foo = %s and bar = '%s'" % (5, 'hello')

Actually you can! Here is the new form:

strSQL = Interp("SELECT * FROM mytable WHERE foo = @1 and bar = @2", 5, "hello")

Note how the first argument is the string and the next ones are the values for the interpolation.

It's even better, since you do not have to worry about quoting: the @2parameter being a string, the function will automatically quote it.

If you use the last argument as a connection chain (starting with DATABASE=, DSN=or ODBC=) the function will automatically figure out the most appropriate syntax for converting dates into the corresponding literal.

Quoting a variable

If you want to quote the variables separately so as to get a litteral, you can use the SQLQuote function:

? SQLQuote(#1/9/2018#, SQL_ORACLE)
TO_DATE("20180901 00:00:00")

The syntax types are:

SQL_JET = 1
SQL_SQLServer = 2
SQL_Oracle = 3
SQL_MYSQL = 4
SQL_UNKNOWN = 5

Database module

Introduction

We all know the DLookup function of access. Suppose we have a table:

ItemNo	ItemName
1	Apple
2	Orange
3	Ananas

Debug.Print DLookup("ItemName", "Item", "ItemNo = 2")
Orange

The result of this function is a Variant and, in this particular case, a string.

Here is the official signature of the function:

 DLookup( expr, domain [, criteria] )

The term domain is used to describe the name of a table, or a query, etc.

What if we generalized this approach? Why not making a similar function that returns a table instead of a Variant?

The DBLookup function

A simple case

Enter the DBLookup function:

Function DBLookup(Domain As String, Optional Filter As String = "", Optional OrderBy As String = "", Optional Connect As String = "") as Collection

It returns the results of the SELECT query in the form of a Collection ("list") of dictionaries.

Compared to the DLookup function, we no longer need the expr argument, since we want all fields of the table:

Dim t As Collection
Dim Row
Set t = DBLookup("Item")
For Each Row In t
    Debug.Print Row("ItemNo"), Row("ItemName")

In the example above, we will get three elements in the table:

1            Apple
2            Orange
3            Ananas

Using filters

Using filters is simple, by writing the filter in SQL syntax, as a string. We will, for example, filter the Item table by the first letter of the name. This is done with the second argument of the DBLookup function:

Dim Row
Dim filter as string
filter = "Left(ItemName,1)='A'"
For Each Row In DBLookup("Item", filter)
    Debug.Print Row("ItemNo"), Row("ItemName")
Next

This will return:

1            Apple
3            Ananas

If you wanted to be explicit, you could write it as: Set row = DBLookup("Item", filter:="Left(ItemName,1)='A'")

If you had the first letter in a variable, the usual way of vba of doing it is messy and frankly ugly:

filter = "Left(ItemName,1)='" & A &"'"

Ugh

We can solve it with our little string interpolation function:

Dim filter As String, letter As String
letter = "A"
filter = Interp("Left(ItemName,1)=@1", letter)

Pheew, better.

The Interp function knows that letter is a string and it needs to be quoted: Left(ItemName,1)='A'. That's good news, because you won't have to mess up again with quotes in strings.

We can now write a decent routine:

Sub TestTable(FirstLetter As String)
    ' Test
    Dim Row
    Dim filter As String

    filter = Interp("Left(ItemName,1)=@1", FirstLetter)
    Debug.Print "Filter is:", filter

    For Each Row In DBLookup("Item", filter)
        Debug.Print Row("ItemNo"), Row("ItemName")
    Next
End Sub

And in the immediate window:

TestTable "A"
Filter is:    Left(ItemName,1)='A'
 1            Apple
 3            Ananas