Pivot Table Connection Relink via VBA causing errors

[graphage]

Good afternoon. I've spent the last few days trying to solve this and can't seem to find an answer. I am hoping someone here can help!

I am using Office 2007.

I have 5 access databases, each having one table in them. I have an Excel Pivot that is linked to one of the 5 Access Database Files and the single table within it. (The tables are large and combined are over 2GB, so I split them into multiple databases).

I am trying to create VBA that will allow me to update the pivot table link based on user selection. The variables will be easy, but I can't even get the root of the code to work!

I recorded a macro and received the following:

Sub RelinkExistingConnection_MakeSurePickNewFile()

    With ActiveWorkbook.Connections("DrillableSalesReport").OLEDBConnection
        .BackgroundQuery = True
        .CommandText = Array("tblDrillableSalesReport_Raw_Military")
        .CommandType = xlCmdTable
        .Connection = Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=J:\Sales\Regional Reporting\Drillable Sale" _
        , _
        "s Report\Raw\DrillableSalesReport_Raw_Military.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";J" _
        , _
        "et OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Glo" _
        , _
        "bal Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=" _
        , _
        "False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fal" _
        , "se;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        .ServerFillColor = False
        .ServerFontStyle = False
        .ServerNumberFormat = False
        .ServerTextColor = False
    End With
    With ActiveWorkbook.Connections("DrillableSalesReport")
        .Name = "DrillableSalesReport"
        .Description = ""
    End With
    ActiveWorkbook.Connections("DrillableSalesReport").Refresh
End Sub

When I try and run the recorded code (w/o making any modification), the first errors I get are related to the 4 lines that start w/ ".Server". Error is "Run-time error '5': Invalid procedure call or argument". Once I comment them out, the code makes it down to the last line of:

ActiveWorkbook.Connections("DrillableSalesReport").Refresh

The error is: Run-time error '1004': Application-defined or object-defined error

If I comment out that line, then the code runs to the end. At this point, I can manually right click the data, click Refresh, and then I have the new data. But, if I try and refresh it via vba, I continue to get error 1004. I have also tried this at the end:

Sheets("DrillableSalesReport").PivotTables("PivotTable2").PivotCache.Refresh

It also generates Run Time error 1004.

Thoughts?

Thank you!