VBA: Refreshing Pivot Table with external data connection

[levo]

In my Excel (Excel 2007 SP2) file I use 2 data sources/connections (let's call them A and B) that are linked to SQL server data.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Both data sources are in separated worksheets in the same file.
<o></o>
<o></o>
There is another connection (C) that refers to data sources A,B and creates/selects data for creating Pivot Table.<o></o>
I can create this Pivot Table using external connection C and can refresh Pivot Table manually.
<o></o>
<o></o>
When trying to refresh Pivot Table using VBA code I receive this Visual Basic error: <o></o>
"Run Time error'1004' [Microsoft][ODBC Excl Driver] The connection for viewing your linked Microsoft Excel worksheet was lost"
<o></o>
<o></o>
To refresh Pivot table I tried using <o></o>

[COLOR=black][FONT=Verdana]ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh<o:p></o:p>[/FONT][/COLOR]

<o></o>
then also <o></o>

[COLOR=black][FONT=Verdana]ActiveSheet.PivotTables("PivotTable2").RefreshTable<o:p></o:p>[/FONT][/COLOR]

<o></o>
but didn't helped. Still got the same error. <o></o>
<o></o>
If I use connection C for getting data to new worksheet, then create Pivot and use the same code to refresh Pivot it works.
Point is that I don’t want to create another data worksheet because it increases file size.

Do I use wrong code or is it impossible to refresh pivot table from external data source using code?

Thank you in advance for any ideas.<o></o>
<o></o>

 

[levo]

No gurus here...or too busy..

It seems that I have found solution on http://p2p.wrox.com/excel-vba/29037-cant-set-commandtext-property-if-cache-has-1-rpt.html.

Replaced "ODBC;DSN" to "OLDB;DSN" in PivotCache Connection.
Refreshed Pivot Table manually.
Both pieces of code (from above) run fine now.