Programmatically setting pivot sourcedata

[hutteto]

Hey,

I am trying to set the pivot SourceData property to a connection
string. When I look at the pt.SourceData I see 2 values. Thre is a
pt.SourceData(1) and pt.SourceData(2).


The first one is the connection string which in this case does not
have the password specified.
pt.SourceData(1) =
"DSN=SDW_PRD_ALLVM;UID=USERID;;DATABASE=SDW_PRD_ALLVM;"

The second one is the SQL:
pt.SourceData(1) = SELECT *FROM sdw_prd_qmtbls.TBL_SO_Non_Sls_Rpt


My goal is to set the connection string (the first one) equal to this:
DSN=SDW_PRD_ALLVM;UID=USERID;PWD=PASSWORD;DATABASE=SDW_PRD_ALLVM;


I've tried setting it using the following 2 methods and each have
failed:
Method 1: Set pt.SourceData(1) = TargetConString 'Gives me error 424
Object Required
Method 2: pt.SourceData(1) = TargetConString 'Doesnt give me an error
but also never sets it


As an alternative I've tried setting the cache:
If I look at pt.PivotCache.Connection I see its equal to
ODBC;DSN=SDW_PRD_ALLVM;UID=rth218;;DATABASE=SDW_PRD_ALLVM;

However when I try to set that connection using the set keyword I get
the same 424 Object required error.
When I remove the set word and run this: pt.PivotCache.Connection = ""
I get Error 1004 Application-defined or object-defined error.

Can anyone tell me how to set the SourceData for this pivot table?"