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

Posted in Uncategorized

3 thoughts on “SQL Query Builder

  1. According to Microsoft’s DLL Help Database ():

    File Name: TTGQuery.dll
    File Version: 3.2.0.1
    File Description: (none)
    Locale: English (United States)

    This file belongs to below software package:
    Microsoft Office Outlook 2003 with Business Contact Manager

    This I do not have :(

    Jamie.

    –


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *