SQL Query Builder

I’m back to my never ending quest to parse SQL statements. This time it’s in connection with my recent work with the Quickbooks SDK. More on that later, but for now I’m trying to take a simple WHERE clause and turn it into the XML that Quickbooks requires. I just don’t think I have it in me to parse every possible combination of fields, operators, values, and functions that could be in the WHERE clause. I was attempting to go back to the Microsoft SQLParser Object and stumbled upon TTG SQL Query Builder (TTGQuery.dll).

This dll apparently has something to do with the Great Plains Accounting Software Package, which I don’t have installed. Mine was found at C:Program FilesMicrosoft OfficeOFFICE11Business Contact ManagerIMTTGQuery.dll. I seem to remember installing the Business Contact Manager, but that could have just been a dream.

It doesn’t solve my parsing problem, but it was pretty interesting. The object model is pretty extensive. Here it is in it’s entirety:

Object Browser window showing ttgquery.dll

You give it a connection object and call a method, it gives you a UI for creating a query. Here’s my first hack at it:

Sub Testttg()

Dim qb As QueryBuilder
Dim cn As ADODB.Connection
Dim sConn As String
Dim sSql As String

sConn = “DSN=MS Access Database;DBQ=C:Documents and SettingsDick.NEBRASKA”
sConn = sConn & “My DocumentsNwind.mdb;DefaultDir=C:Documents and Settings”
sConn = sConn & “Dick.NEBRASKAMy Documents;DriverId=281;”
sConn = sConn & “FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”

Set qb = New QueryBuilder

Set cn = New ADODB.Connection

cn.Open sConn

Set qb.ADOConnection = cn
sSql = qb.GetSQLStatement(False)

MsgBox sSql

End Sub

That gets me this

Query Builder dialog

The dialog returns a string (an empty string if you cancel) that I display in a message box. I can’t figure out what the argument to GetSQLStatement is. It’s labeled Cancel, but True and False give me the same result. As far as I can tell, you can create SQL strings, but not edit them. There appears to be no way to pass a SQL statement into the dialog.

message box showing sql statement