Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs

[mdlueck]

I had been seeing various posts of people building "search forms" in Access, needing to build the correct query string based on the populated search form values. So, I thought to myself... "I already know how to read the controls of the form (my Validation classes do that), and I have working example code of how to use ADODB.Command / .Parameters objects to run SELECT queries. So why not harness ADODB.Command / .Parameters objects to build a dynamic query with a variable number of .Parameters objects?!"

Sample code performing a SQL SELECT with ADODB.Command / .Parameters objects:

Public Function LocateByPartNumber() As Boolean
On Error GoTo Err_LocateByPartNumber

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim strSQL As String

  'Define a query to look for the KeyValue based on the KeyName and Run-Time Environment
[B][COLOR=Red]  strSQL = "SELECT [piw].[aid],[piw].[title],[piw].[qtyper],[oldqtyper],[piw].[addpartrecordflg],[piw].[doneflg] " & _
           "FROM [" & Me.FETempTableName & "] AS [piw] " & _
           "WHERE [piw].[partnumber] = ?;"[/COLOR][/B]

  'Define attachment to database table specifics
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = strSQL
    [COLOR=Red][B].Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)[/B][/COLOR]
    Set adoRS = .Execute()
  End With

  With adoRS
    'Was no record found?
    If .BOF Or .EOF Then
      Me.Clear
      LocateByPartNumber = False
    Else
      'Fetch the value found
      Me.aid = Nz(adoRS!aid, 0)
      Me.title = Nz(adoRS!title, vbNullString)
      Me.qtyper = Nz(adoRS!qtyper, 0)
      Me.oldqtyper = Nz(adoRS!oldqtyper, 0)
      Me.addpartrecordflg = Nz(adoRS!addpartrecordflg, False)
      Me.doneflg = Nz(adoRS!doneflg, False)
      LocateByPartNumber = True
    End If

    'Close the database table
    .Close
  End With

Exit_LocateByPartNumber:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

Err_LocateByPartNumber:
  Call errorhandler_MsgBox("Class: clsObjPartsImportWizardTbl, Function: LocateByPartNumber()")
  LocateByPartNumber = False
  Resume Exit_LocateByPartNumber

End Function

All that should need to happen is:

1) Template the SQL query, base part + dynamic WHERE CLAUSE... loop through the fields populated and hard code AND / OR binary operations
2) For the .Parameters objects, I already am calling the .CreateParameter method already, so just need to:
2a) Keep a step counter to dynamically / sequentially name the parameters objects. Ex: p1, p2, p3, p4... While Access does not require such, I still do it for good house keeping. Much easier to see what is what in the watches window if you follow such a standard
2b) Need some sort of static reference table to keep track of the field's property values required when creating the .Parameters objects. Simple in-memory collection class, keyed by table/column and then you receive back the rest of the details required in the .CreateParameter call.

The appeal of performing queries with ADODB.Comamnd / .Parameters objects is the greater ease of dealing with building the string, NOT having to fight with quote characters or needing to escape characters. Once a variable piece of data goes inside its respective .Parameters objects... it is encapsulated, ready to be handed off to the query engine. That is a SO COOL feature of queries via ADODB.Comamnd / .Parameters objects!!

Oh..... I see why I have not tried coding this up yet... I have not found a way to totally variablize the call to .CreateParameter so that it may be put into a loop. All right, digging into my Object Rexx bag of tricks... INTERPRET to the rescue! I recall VBA had something roughly equivalent... but I seem to recall it can not work with objects... gggrrr..... Aaahhh yes, Eval() is what is in VBA.

Has anyone ever tried such with ADODB.Comamnd / .Parameters objects?

 

[mdlueck]

Oh..... I see why I have not tried coding this up yet... I have not found a way to totally variablize the call to .CreateParameter so that it may be put into a loop.

All right, I can handle this as well. One must statically type all of the .Parameters.Append .CreateParameter() LOC's, protect each one with an IF statement testing if the value was provided in the search form, and then use a step counter to dynamically compute the correct Parameter number (p1, p2, p3...) which may be variable.

So this is a likely scenario to actually work.