Excel VBA: "application-defined or object-defined error"

Question

In Excel 2003 I'm getting a Runtime error 1004: "application-defined or object-defined error" on the last line of this code (commandtext = abc)

Sub SCommandTxt()
Dim abc as string
abc = Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText 
Sheets("Totals").PivotTables("PivotTable2").PivotCache.CommandText = abc
End Sub

This isn't really what I'm trying to do, but not knowing what is causing an error in something as simple as this is driving me up a wall. The Pivot table at hand is an ODBC connection. The following code was run before this code and works fine. All I really want to do is change the query dynamically based on the changing range "WhereFilters". The below query works alright, but I'd prefer not to have to unhide and select the sheet and go through the pivotwizard if I can just change the commandText directly (though based on the errors I'm getting maybe not... Though others seem to think the above is possible, so I don't know why it isn't working for me):

Sub UpdatePvt()
Dim DBDir As String, DBName As String, SortType As String, Size As String
Dim QueryArry1(0 To 100) As String, rng As Range, x As Integer

DBDir = "C:\Documents and Settings\jt\"
DBName = "DatabaseExample.mdb"

If Range("ComboResult1") = 1 Then
    SortType = "TDollars"
    Sheets("Totals").PivotTables("PivotTable1").PivotFields("DIV_ID").AutoSort _
        xlDescending, "Sum of Dollars"
    Sheets("Totals").PivotTables("PivotTable2").PivotFields("DIV_ID").AutoSort _
        xlDescending, "Sum of Dollars"
Else
    SortType = "TCounts"
    Sheets("Totals").PivotTables("PivotTable1").PivotFields("DIV_ID").AutoSort _
        xlDescending, "Sum of Counts"
    Sheets("Totals").PivotTables("PivotTable2").PivotFields("DIV_ID").AutoSort _
        xlDescending, "Sum of Counts"
End If

If Range("ComboResult2") = 1 Then
    Size = "Total"
ElseIf Range("ComboParOUT") = 2 Then
    Size = "Small"
Else
    Size = "Large"
End If

QueryArry1(0) = "SELECT Top 500 C.* "
QueryArry1(1) = "FROM Final03 C "
x = 2
If Not (Range("NoFilters")) Then
    QueryArry1(x) = "INNER JOIN (Select DIV_ID FROM FullLookup WHERE "
    x = x + 1

    For Each rng In Range("WhereFilters")
        QueryArry1(x) = rng.Value
        x = x + 1
    Next rng

    QueryArry1(x) = "GROUP BY DIV_ID) E ON C.DIV_ID = E.DIV_ID "
    x = x + 1
End If
QueryArry1(x) = "WHERE C.EntitySize = '" & Size & "' "
QueryArry1(x + 1) = "ORDER BY C." & SortType & " DESC "

'Example Query Results:
'SELECT Top 500 C.* FROM Final03 C INNER JOIN (Select DIV_ID FROM FullLookup WHERE Year = 2008 and State = 'MN' and Type = 'RST44' GROUP BY DIV_ID) E ON C.DIV_ID = E.DIV_ID WHERE C.EntitySize = 'Large' ORDER BY C.TCounts DESC 

Sheets("Totals").Visible = xlSheetVisible

Sheets("Totals").Select
Sheets("Totals").PivotTables("PivotTable1").DataBodyRange.Select
Sheets("Totals").PivotTableWizard SourceType:=xlExternal, _
    SourceData:=QueryArry1, _
    Connection:=Array( _
        Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _
        Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
    )

Sheets("Totals").PivotTables("PivotTable2").DataBodyRange.Select
Sheets("Totals").PivotTableWizard _
    SourceType:=xlPivotTable, _
    SourceData:="PivotTable1"

Sheets("Totals").Visible = xlSheetHidden
End Sub

Thanks

Answer 1

Your problem appears to be the exact one described here:

Limitation of PivotCache.CommandText property

How long is the string you're trying to set as CommandText?