title: trouble changing commandText of odbc connection with vba
body: <p>I have spent two days looking for a solution, this is driving me nuts...</p>
  
  <p>First let me explain what I'm doing. We're bringing in half a million records, with these records driving a dozen pivot tables.  To keep the workbook manageable file size, I built the pivot table directly from the external data connection. It's an odbc connection that I configured manually.</p>
  
  <p>It all works fine, I can hit "refresh all" in the workbook and all the pivot tables update automatically, wonderful.</p>
  
  <p>But now I need to be able to restrict the entire recordset with manual start and end date... changing the date filters on the pivot tables is not ideal, because it is not just a single field affected by the end date, there are fields that need to be calculated prior to the pivoting, which have values that depend on formulas involving the end date.</p>
  
  <p>After a whole afternoon of crashing Excel repeatedly, I figured out the restriction that if your connection is directly to a pivot table, you can't use ? and the parameter dialog to point to a cell reference, the cell references are lost once you close the book.</p>
  
  <p>So my next approach was to do this:</p>
  
  <pre><code>Dim ReportStartDate, ReportEndDate
  
  ' Get parameters from Intro sheet
  ReportStartDate = "'" &amp; ActiveWorkbook.Worksheets("Intro").Range("$B$1").Value &amp; "'"
  ReportEndDate = "'" &amp; ActiveWorkbook.Worksheets("Intro").Range("$B$2").Value &amp; "'"
  
  ' There are 3 directpivot odbc connections/caches that need to be modified.
  ' In each query, the default report-end-date is specified by CURDATE().
  ' The default report-start-date is specified as '2010-01-01'
  ' Replace these defaults with the values retrieved above.
  
  Dim cn As WorkbookConnection
  Dim odbcCn As ODBCConnection
  Dim originalsqltext, newsqltext
  
  For Each cn In ThisWorkbook.Connections     ' loop through the connections
      If cn.Type = xlConnectionTypeODBC Then
          Set odbcCn = cn.ODBCConnection
          originalsqltext = odbcCn.CommandText
          If odbcCn.Parent = "Calls" Then
              newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
              newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
          ElseIf odbcCn.Parent = "Suboutcomes" Then
              newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
              newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
          ElseIf odbcCn.Parent = "QtyCallsPerDay1" Then
              newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
          Else
              newsqltext = originalsqltext
          End If
          odbcCn.CommandText = newsqltext
          odbcCn.Refresh
          odbcCn.CommandText = originalsqltext
      End If
      cn.Refresh ' refresh the other connection without modification
  Next
  Set cn = Nothing
  Set odbcCn = Nothing
  </code></pre>
  
  <p>But it's throwing me an error when it gets to the odbcCn.CommandText = newsqltext
  Run-time error '1004:
  Application-defined or object-defined error.
  which is so not helpful...</p>
  
  <p>I verified that newsqltext contained what I intended, it just won't assign back into CommandText.</p>
  
  <p>After another day of googling, and some simple macro-recording experiments, it looks like changing the CommandText requires syntax like</p>
  
  <pre><code>.CommandText = Array( _
  "SELECT C1.CALLID, C1.TAKENON, C1A.TAKENAT, CAST(CONCAT(DATE_FORMAT(TAKENON,'%c/%e/%y'),' ',TIME_FORMAT(TAKENAT,'%H:" _
      , _
      "%i:%s')) AS CHAR) AS CallDateTime, YEAR(C1.TAKENON) AS Year, CEILING(MONTH(C1.TAKENON)/3) AS Quarter, MONTH(C1.TAKE" _
      , _
  </code></pre>
  
  <p>(I'm leaving off the rest because it's huge)... at first I thought that was my problem, since when I tried recording the macro initially, I was getting a "too many line continuations" error, so I shortened the query as much as possible, getting it down to 1428 chars before the substitution. After substitution, it ends up at 1448 chars... but how do I parse it into the array format the code wants? Or is there some better way to do this?</p>
  
  <p>I really don't want to mangle my queries like this, just to be able to edit them with vba, and I feel like I'm just missing something for how to alter the CommandText...</p>
  
  <p>There were a couple of troubling things that my searching turned up, like this issue about not being able to change the CommandText on an odbc connection unless you changed it to an oledb first, then you could change the CommandText, then change the connection back to odbc... but that was prior to Excel 2010, which no longer uses these... <a href="http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html" rel="nofollow noreferrer">http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html</a></p>
  
  <p>The KnowledgeBase article linked to in there, <a href="http://support.microsoft.com/kb/816562" rel="nofollow noreferrer">http://support.microsoft.com/kb/816562</a>, is even more alarming... I thought I was onto a solution when I saw the StringToArray function, but then I read further and saw </p>
  
  <blockquote>
    <p>Note The previous code may not work as you expect if you are using shared PivotCaches, an OLAP-based PivotTables, or a Multiple Consolidation Range-based PivotTables to connect to the database.</p>
  </blockquote>
  
  <p>and then</p>
  
  <blockquote>
    <p>If multiple PivotTables on a worksheet are derived from the same PivotTable, the subroutine does not work after it processes the first PivotTable. As of March 2003, there is no known workaround for this problem. </p>
  </blockquote>
  
  <p>although it notes that the article only applies to Excel 2000 through 2003.</p>
  
  <p>One other thing I tried, I thought maybe I could use ? parameters, and just set them with vba... but when I created a simple query with parameters, then recorded a macro while I pointed the parameters to new cell references, the macro contained only this:
      Sub PARAMEDIT5()
      '
      ' PARAMEDIT5 Macro
      '</p>
  
  <pre><code>'
      With ActiveWorkbook.Connections("PARAMEDIT").ODBCConnection
          .BackgroundQuery = False
          .CommandText = Array("SELECT * FROM Calls1 where TAKENON &gt; ?" _
          )
          .CommandType = xlCmdSql
          .Connection = _
          "ODBC;DRIVER={MySQL ODBC 5.1 Driver};UID=xxxxxxx;PWD=xxxxxxxx;SERVER=xxxxxx;PORT=3306;BIG_PACKETS=1;"
          .RefreshOnFileOpen = False
          .SavePassword = True
          .SourceConnectionFile = ""
          .SourceDataFile = ""
          .ServerCredentialsMethod = xlCredentialsMethodIntegrated
          .AlwaysUseConnectionFile = False
      End With
      With ActiveWorkbook.Connections("PARAMEDIT")
          .Name = "PARAMEDIT"
          .Description = ""
      End With
      ActiveWorkbook.Connections("PARAMEDIT").Refresh
  End Sub
  </code></pre>
  
  <p>I tried this with both the direct-into-pivot type of connection, and with a regular table connected to an external data source, which I know does support parameters.</p>
  
  <p>So... does anyone know what is the correct way to parameterize the query for a shared pivot cache odbc connection ?  </p>
  
  <p>UPDATE:
  I tried this:</p>
  
  <pre><code>Dim cn, originalCn, newCn As WorkbookConnection
  Dim odbcCn As ODBCConnection
  Dim originalsqltext, newsqltext
  Dim connStr As String
  
  For Each cn In ThisWorkbook.Connections     ' loop through the connections
      If cn.Type = xlConnectionTypeODBC Then
          Set odbcCn = cn.ODBCConnection
          originalsqltext = odbcCn.CommandText
          Set originalCn = cn
          connStr = odbcCn.Connection
          Select Case odbcCn.Parent
              Case "Calls", "Suboutcomes"
                  newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
                  newsqltext = Replace(newsqltext, "'2010-01-01'", ReportStartDate)
              Case "QtyCallsPerDay1"
                  newsqltext = Replace(originalsqltext, "CURDATE()", ReportEndDate)
              Case Else
                  newsqltext = originalsqltext
          End Select
          Set newCn = ActiveWorkbook.Connections.Add(odbcCn.Parent &amp; "New", "WhoCares", connStr, newsqltext)
          Set cn = newCn
          cn.Refresh
          Set cn = originalCn
          newCn.Delete
      Else
          cn.Refresh ' refresh any other connections without modification
      End If
  Next
  
  Set cn = Nothing
  Set odbcCn = Nothing
  Set newCn = Nothing
  Set originalCn = Nothing
  </code></pre>
  
  <p>And while it seems to do what I want in terms of getting the commandtext to what I want, cn.Refresh does nothing when I step through. If it was refreshing, but my pivots weren't updating, I could see where maybe they're looking for Calls1 and at the moment the refresh happens, it's named to Calls1New, but the connection is just not doing anything (the query usually takes a few minutes to complete).  Or maybe I can't assign it to the existing connection with the same name?  After I set cn = newCn, they both look exactly the same, with same name.</p>
  
  <p>I'll poke around some more, but if anybody else has done something like this, I'd appreciate more help. Thanks much for what you've given so far!</p>
  
  <p><strong>EDIT</strong>: so I'm back to the original</p>
  
  <pre><code>odbcCn.CommandText = newsqltext
  cn.Refresh
  odbcCn.CommandText = originalsqltext
  </code></pre>
  
  <p>I also tried 
      odbcCn.CommandText = StringToArray(newsqltext)
      cn.Refresh
      odbcCn.CommandText = StringToArray(originalsqltext)
  that I found at <a href="http://support.microsoft.com/kb/816562" rel="nofollow noreferrer">http://support.microsoft.com/kb/816562</a>. neither one worked.</p>
  
  <p>I'll post the originalsqltext and newsql text, as they are right before the error. Note the the originalsqltext works fine if I manually paste it into the dialog box for the query, <strong>as does newsqltext</strong></p>
  
  <p>**removed earlier edit, due to new information **</p>
  
  <p>note - I found a thread <a href="https://stackoverflow.com/questions/1867459/excel-vba-update-pivot-sourcedata">Excel VBA: Update Pivot Sourcedata</a> that seems like a similar problem - because I have tested trying to assign odbcCn.CommandText = originalsqltext (which hasn't been altered in any way) and it fails also.  However, this thread is from 2009, so it is most likely not using excel 2010, because I tried to write </p>
  
  <pre><code>For Each pvtC In ThisWorkbook.PivotCaches
      name = pvtC.WorkbookConnection.name
      originalsqltext = pvtC.CommandText
      pvtC.CommandText = originalsqltext
  Next
  </code></pre>
  
  <p>and it also fails right at pvtC.CommandText = originalsqltext</p>
  
  <p><strong>UPDATE:</strong>
  I'm certain now that this has nothing to do with the query itself, but rather the condition of having multiple pivot tables pointed to the same pivotcache.
  I created a new external datasource with the simple query</p>
  
  <pre><code>SELECT * FROM clientdashboard1.Calls1 WHERE TAKENON BETWEEN '2010-01-01' AND CURDATE()
  </code></pre>
  
  <p>as its query.  I named the connection AlphaTest, and created a pivot table from it, then copied that pivot table to another sheet and used different fields.  I modified my code to run through this first:</p>
  
  <pre><code>For Each pvtC In ThisWorkbook.PivotCaches
      name = pvtC.WorkbookConnection.name
      If name = "AlphaTest" Then
          originalsqltext = pvtC.CommandText
          pvtC.CommandText = originalsqltext
      End If
  Next
  </code></pre>
  
  <p>It failed at exactly the same point, pvtC.CommandText = originalsqltext</p>
  
  <p>then I removed the second pivot table, and stepped through again, and it worked.</p>
  
  <p>Then just for kicks, I plopped my original huge query in, and stepped through again. It worked.  However, it uncovered another wrinkle... changing CommandText via code causes it to refresh.  So my original plan, of making substitutions, doing a refresh, and then setting back to the original, is not going to work, as the tables will refresh again at the second assignment (if it worked, that is).</p>
  
  <p><strong>UPDATE</strong> This just gets better and better. I though about making dummy copies of my pivot caches, with just 1 single record perhaps, point each pivot table to its own cache, then have vba alter the query of the "real" one, then loop through and point each pivot table to that one.  Do the reporting tasks (copy out sheets, break links, save-as, close is how we usually do it). Then back in the original book, point all the pivot tables to their respective dummy caches. Well, you know that AlphaTest datasource that I had? I thought that pvtC.CommandText = originalsqltext actually changed the query and caused AlphaTest to refresh... oh no my friends.  It created a new connection, called Connection, which is a copy of AlphaTest.
  Ok Fine.  How can I use this ? ..... I have some ideas I'm going to try, but if anyone else has dealt with this, please, please.... I'm going to try something like the approach Tim suggested... it's just that I won't be able to directly alter the pivotcache that the tables are connected to, I'll have to have them hooked to a default shared connection, have an editable connection (with no pivot tables connected), edit that one with my parameters, point each pivot table to it, do the reporting tasks, then point the pivot tables back to the default shared connection... If this works I'll be very happy.</p>