change commandtext in pivotcaches

[patrick]

hello

i want to change the commandtext in pivotcaches

i've an odbc connection, then i create e pivot cache and a pivot table:
With ActiveWorkbook.PivotCaches.Add(xlExternal)
.Connection = _
"ODBC;DSN=test;UID=test;PWD=test"
.CommandText = (sql-statement)
.CreatePivotTable Range("A3"), "Pivot Table"
End With

then in step 2 i want to change the sql statement (p.e. with new date
selection):

With ActiveWorkbook.PivotCaches.Add(xlExternal)
.CommandText = (sql-statement 2)
End With

ActiveSheet.PivotTables("Pivot Tabelle").PivotCache.Refresh

but the cache wasn't refresh on the pivot table. the pivot table looks
with the old date too.

i also try:
set pvtCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
but i work doesn't too.

can anyone help me, please?

 

[Dick Kusleika]

Patrick

It looks like you are creating a new PivotCache, but nowhere do you change
the PivotCache that's linked to the PivotTable that you are refreshing. If
I understand correctly, you would want something like this

With ActiveSheet.PivotTables("Pivot Tabelle")
.PivotCache.CommandText = sql-statement 2
.Refresh
End With

It seems that you don't need the Add method to create a new PivotCache, just
change the CommandText property of the existing one.

 

[patrick]

okay, thanks, but then i have a runtime error 1004 on the line where i
want to set the sql-statement 2...

perhaps it's the sql-statement. if i use an easy sql-statement, then it
works.
but i use the same sql-statement like the first one. it has just another
strings from the inputform.

it looks like this:

"SELECT ... FROM ... WHERE Date>='" & sDate1 & "' AND Date<='" & sDate2
& "'"

any ideas?

 

[Dick Kusleika]

Patrick

It may be that sDate1 and sDate2 are in the wrong format. I presume they
are strings and not actual dates. If they are actual dates, then you should
use the Format function to get them in the correct format. Next, look at an
SQL statement that works with dates in the WHERE clause. Before you change
the PivotCache.CommandText, add a line like this

Debug.Print .PivotCache.CommandText

and look closely at the way it reads particularly around the dates. I've
seen different programs use slightly different formats for dates, such as

'9/2/2003'
#9/2/2003#
'2003-09-02'

Just make sure you sDate variables and the string that you are creating for
the SQL match up to what you know works.

 

[davidmac]

did this ever get resolved?

i have exactly the same issue.

 

[nboisen]

Resolved

I found a solution to this problem on another site.
Basica;lly, you need to temporarily change the "QueryType" from ODBC to OLE.

If you attempt to change the command text with QueryType set to ODBC, a runtime error occurs. If you change to OLE, you can change the commandtext and then revert back to ODBC.

You can see the details here: http://p2p.wrox.com/topic.asp?TOPIC_ID=30220

The sample code follows:
With ActiveWorkbook.PivotCaches(intPCIndex)
If .QueryType = xlODBCQuery Then
blnODBCConnect = True
strSQLConnect = Replace(strSQLConnect, "ODBC;DSN", "OLEDB;DSN", 1, 1, vbTextCompare)
End If

If StrComp(.Connection, strSQLConnect, vbTextCompare) <> 0 Then
.Connection = strSQLConnect
End If

If StrComp(.CommandText, strSQL, vbTextCompare) <> 0 Then
.CommandText = strSQL
End If

If blnODBCConnect = True Then
.Connection = Replace(.Connection, "OLEDB;DSN", "ODBC;DSN", 1, 1, vbTextCompare)
End If

.Refresh
End With


Notes:

• Basically, I just check to see if the QueryType property of my PivotCache object is ODBC. If so, I replace "ODBC" with "OLEDB" in the Connection property, make my changes to the CommandText property, then replace "OLEDB" back with "ODBC" in the Connection property
• intPCIndex is a variable I set based on the Index property of a PivotCache object (also can be gotten as the CacheIndex property of a PivotTable object)
• strSQLConnect and strSQL are variables that I am populating with connection string and query text data (respectively) from one of my worksheets