trouble changing commandText of odbc connection with vba

Question

I have spent two days looking for a solution, this is driving me nuts...

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.

It all works fine, I can hit "refresh all" in the workbook and all the pivot tables update automatically, wonderful.

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.

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.

So my next approach was to do this:

Dim ReportStartDate, ReportEndDate

' Get parameters from Intro sheet
ReportStartDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B$1").Value & "'"
ReportEndDate = "'" & ActiveWorkbook.Worksheets("Intro").Range("$B$2").Value & "'"

' 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

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...

I verified that newsqltext contained what I intended, it just won't assign back into CommandText.

After another day of googling, and some simple macro-recording experiments, it looks like changing the CommandText requires syntax like

.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" _
    , _

(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?

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...

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... http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html

The KnowledgeBase article linked to in there, http://support.microsoft.com/kb/816562, is even more alarming... I thought I was onto a solution when I saw the StringToArray function, but then I read further and saw

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.

and then

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.

although it notes that the article only applies to Excel 2000 through 2003.

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 '

'
    With ActiveWorkbook.Connections("PARAMEDIT").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM Calls1 where TAKENON > ?" _
        )
        .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

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.

So... does anyone know what is the correct way to parameterize the query for a shared pivot cache odbc connection ?

UPDATE: I tried this:

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 & "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

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.

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!

EDIT: so I'm back to the original

odbcCn.CommandText = newsqltext
cn.Refresh
odbcCn.CommandText = originalsqltext

I also tried odbcCn.CommandText = StringToArray(newsqltext) cn.Refresh odbcCn.CommandText = StringToArray(originalsqltext) that I found at http://support.microsoft.com/kb/816562. neither one worked.

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, as does newsqltext

**removed earlier edit, due to new information **

note - I found a thread Excel VBA: Update Pivot Sourcedata 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

For Each pvtC In ThisWorkbook.PivotCaches
    name = pvtC.WorkbookConnection.name
    originalsqltext = pvtC.CommandText
    pvtC.CommandText = originalsqltext
Next

and it also fails right at pvtC.CommandText = originalsqltext

UPDATE: 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

SELECT * FROM clientdashboard1.Calls1 WHERE TAKENON BETWEEN '2010-01-01' AND CURDATE()

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:

For Each pvtC In ThisWorkbook.PivotCaches
    name = pvtC.WorkbookConnection.name
    If name = "AlphaTest" Then
        originalsqltext = pvtC.CommandText
        pvtC.CommandText = originalsqltext
    End If
Next

It failed at exactly the same point, pvtC.CommandText = originalsqltext

then I removed the second pivot table, and stepped through again, and it worked.

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).

UPDATE 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.

Solution 1

After doing a lot of research online... I'm finding that this is a bug when updating the CommandText property of an ODBC connection. If you temporarily switch to an OLEDB connection, update your CommandText property and then switch back to ODBC it does not create the new connection. Don't ask me why... this just works for me.

Create a new module and insert the following code:

Option Explicit

Sub UpdateWorkbookConnection(WorkbookConnectionObject As WorkbookConnection, Optional ByVal CommandText As String = "", Optional ByVal ConnectionString As String = "")

With WorkbookConnectionObject
    If .Type = xlConnectionTypeODBC Then
        If CommandText = "" Then CommandText = .ODBCConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .ODBCConnection.Connection
        .ODBCConnection.Connection = Replace(.ODBCConnection.Connection, "ODBC;", "OLEDB;", 1, 1, vbTextCompare)
    ElseIf .Type = xlConnectionTypeOLEDB Then
        If CommandText = "" Then CommandText = .OLEDBConnection.CommandText
        If ConnectionString = "" Then ConnectionString = .OLEDBConnection.Connection
    Else
        MsgBox "Invalid connection object sent to UpdateWorkbookConnection function!", vbCritical, "Update Error"
        Exit Sub
    End If
    If StrComp(.OLEDBConnection.CommandText, CommandText, vbTextCompare) <> 0 Then
        .OLEDBConnection.CommandText = CommandText
    End If
    If StrComp(.OLEDBConnection.Connection, ConnectionString, vbTextCompare) <> 0 Then
        .OLEDBConnection.Connection = ConnectionString
    End If
    .Refresh
End With

End Sub

This UpdateWorkbookConnection subroutine only works on updating OLEDB or ODBC connections. The connection does not necessarily have to be linked to a pivot table. It also fixes another problem and allows you to update the connection even if there are multiple pivot tables based on the same connection.

To initiate the update just call the function with the connection object and command text parameters like this:

UpdateWorkbookConnection ActiveWorkbook.Connections("Connection"), "exec sp_MyAwesomeProcedure"

You can optionally update the connection string as well.

I never use the ? parameters so i can't really tell if this will fix your issues with that, but I suspect it will. I always just use string concatenation to insert the parameters directly into the CommandText string.

Solution 2

I use ODBC connections all the time. I first establish my connection manually on a worksheet. I make sure the "Enable Background Refresh" is turned off. I do this stuff all day long. Here's some simple code to refresh the connection (Dim your variables as necessary):

ActiveWorkbook.Connections("ExampleConnection").Refresh

To change the command text:

ActiveWorkbook.Connections("ExampleConnection").ODBCConnection.CommandText = _
"SELECT FILE1.FIELD1 AS ""Name1"", FILE1.FIELD2 as ""Name2""" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"FROM SERVER.LIBRARY.FILE1 FILE1" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"WHERE FILE1.FIELD1 = 'FILTER'"
ActiveWorkbook.Connections("ExampleConnection").Refresh

To change the command text with a run-time variable:

DIM str AS STRING

str = "VARIABLE"

ActiveWorkbook.Connections("ExampleConnection").ODBCConnection.CommandText = _
"SELECT FILE1.FIELD1 AS ""Name1"", FILE1.FIELD2 as ""Name2""" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"FROM SERVER.LIBRARY.FILE1 FILE1" & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
"WHERE FILE1.FIELD1 = '" & str & "'"
ActiveWorkbook.Connections("ExampleConnection").Refresh

And to refresh multiple pivots, as I mentioned above, make sure your ODBC connection "Enable Background Refresh" checkbox is unchecked and you can refresh pivots all day long:

ActiveWorkbook.Connections("ExampleConnection").Refresh
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
Sheet1.PivotTables("PivotTable2").PivotCache.Refresh
Sheet2.PivotTables("PivotTable1").PivotCache.Refresh
Sheet2.PivotTables("PivotTable2").PivotCache.Refresh

An option, either for a dynamic WHERE clause or to get around the "Too many lines" error:

DIM s AS STRING
DIM f AS STRING
DIM w AS STRING
DIM r AS RANGE
Dim str AS STRING

set r = Sheet1.Range("A1")
str = r.Value
s = "SELECT FILE1.FIELD1 as ""Name1"", FILE1.FIELD2 as ""Name2"""
f = "FROM SERVER.LIBRARY.FILE1 FILE1"

If r.Value = "" Then
   w = ""
Else
   w = "WHERE FILE1.FIELD1 = '" & str & "'"
End If

ActiveWorkbook.Connections("ExampleConnection").ODBCConnection.CommandText = _
s & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
f & chr(13) & "" & chr(10) & chr(13) & "" & chr(10) & _
w
ActiveWorkbook.Connections("ExampleConnection").Refresh