title: parameterized queries in Excel with SQLite ODBC
body: <p>I'm using the <a href="http://www.ch-werner.de/sqliteodbc/" rel="nofollow">SQLite ODBC Driver</a> v0.9993 in Excel 2016 (win10). The main impetus for using an external source is that I have too much data for Excel to sanely manage, so it will be pulled based on user-selected criteria. Because of this, I'd like to use parameterized queries based on the contents of well-defined worksheet cells.</p>
  
  <p>I'm trying two methods:</p>
  
  <ol>
  <li><p>Straight VBA, where I do something like this (rough code):</p>
  
  <pre><code>Sub UpdateTables()
      Dim ws as Worksheet
      Dim adoCN As ADODB.Connection
      Dim adoCmd As ADODB.Command
      Dim adoRS As ADODB.Recordset
      Dim sDB as String
      Dim rCell as Range
      Set adoCN = New ADODB.Connection
      Set adoRS = New ADODB.Recordset
      ' ws is set to correct worksheet
      ' ...
      ' define sDB from worksheet cell
      With adoCN
          .Open "Provider=MSDASQL.1;Persist Security Info=True;" _
              &amp; "Driver={SQLite3 ODBC Driver};" _
              &amp; "Database=" &amp; sDB &amp; ";" _
              &amp; "DSN=SQLite3 Datasource;LongNames=true;fksupport=true", "", "" '
      End With
      Set adoCmd = New ADODB.Command
      adoCmd.ActiveConnection = adoCN
      ' rCell points to cell containing query parameter
      Set adoParam = adoCmd.CreateParameter(, adVarChar, adParamInput, _
          Len(rCell.value), rCell.value)
      adoCmd.Parameters.Append adoParam
      adoCmd.CommandText = "SELECT * FROM TableName WHERE X = ?"
      adoRS.Open Source:=adoCmd, CursorType:=adOpenKeyset
      With ws.ListObjects(1).QueryTable
          Set .RecordSet = adoRS
          .Refresh ' errors with "Error 1004: invalid accessor flag"
      End With
  End Sub
  </code></pre>
  
  <p>(Code has been simplified, generally I include sanity checks.)</p></li>
  <li><p>GUI-based in Excel, with <em>New Query</em> > <em>From Other Sources</em> > <em>From ODBC</em>, set DSN to "SQLite3 Datasource", and enter in the Connection string used above.</p>
  
  <p>Unfortunately, the "Parameters" button (<em>Connections</em> > <em>select query</em> > <em>Properties</em> > <em>Definition</em> tab) is grayed out.</p></li>
  </ol>
  
  <p>I think I prefer the second solution, but neither is working at the moment.</p>