Database

Introduction

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

It creates an abstraction level where a table or result of a query is an object (list of dictionaries) that can be easily consulted.

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" % "'"

This is conducive to syntax errors, eyestrain and even combat fatigue. 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 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 e.g.:

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 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! How did the creators of Visual Basic for Applications let us live for years with that monstrosity? Did they have a heart at all?

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. Since letteris a string, the Interp function knows that it needs to be quoted.

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