title: Unable to Set PivotTable to Workbook Connection (ACCESS DB)
body: <p><strong>Goal</strong>: to connect my Pivot Table directly to the source DB table (in MS-ACCESS 2010). </p>
  
  <p><strong>Status</strong>: I am able to set up the <code>WorkbookConnection</code> to the DB via VBA.
  I am also able to create the <code>PivotTable</code> by using the code:</p>
  
  <pre><code>WB.PivotCaches.Create(SourceType:=xlExternal, SourceData:=Conn) _
                  .CreatePivotTable TableDestination:=Worksheets("ManHours").Range("A1"), _
                  TableName:="OnePager"
  </code></pre>
  
  <p>However, trying to <code>Set PvtTbl</code> with the 2 options below produces a Run-Time Error (1004):</p>
  
  <p><em>Option 1</em>:</p>
  
  <pre><code>Set PvtTbl = PvtCache.CreatePivotTable(TableDestination:=Worksheets("ManHours").Range("A1"), TableName:="OnePager")
  </code></pre>
  
  <p><em>Option 2</em>:</p>
  
  <pre><code>Set PvtTbl = Worksheets("ManHours").PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Worksheets("ManHours").Range("A1"), TableName:="OnePager")
  </code></pre>
  
  <p><strong>My Code</strong></p>
  
  <pre><code>Option Explicit
  
  Sub Dynamic_PivotTable()
  
  Dim WB As Workbook
  Dim WBConns As WorkbookConnection
  Dim Conn    As WorkbookConnection
  Dim ConnString As String
  
  Dim PvtTbl      As PivotTable
  Dim PvtCache    As PivotCache
  
  Set WB = Workbooks("DynamicPivot_Connection.xlsm") '&lt;-- Workbook is open
  
  ' define ConnectionString
  ConnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=J:\PMO\Resource allocation\DB\Resource_DB.accdb;" &amp; _
              "Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" &amp; _
              "Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" &amp; _
              "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" &amp; _
              "Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" &amp; _
              "Jet OLEDB:Bypass UserInfo Validation=False"
  
  ' loop through all Workbook connections and delete all except "Resource_DB"
  For Each WBConns In WB.Connections
      Select Case WBConns.Name
          Case "Resource_DB"
              Set Conn = WBConns
          Case Else
              WBConns.Delete        
      End Select
  Next WBConns
  
  If Conn Is Nothing Then
      Set Conn = WB.Connections.Add("Resource_DB", "Resource DB Full Data Set", ConnString, "Select * from MergedDBTbl", 3)
  End If
  
  ' set the Pivot Cache to update Workbook connection
  Set PvtCache = WB.PivotCaches.Add(xlExternal, Conn)
  
  ' add this line in case the Pivot table doesn't exit &gt;&gt; first time running this Macro
  On Error Resume Next
  Set PvtTbl = Worksheets("ManHours").PivotTables("OnePager") ' check if "OnePager" Pivot Table already created (in past runs of this Macro)
  
  On Error GoTo 0
  If PvtTbl Is Nothing Then
  
      ' create a new Pivot Table in "ManHours" sheet, start from Cell A1                   
      WB.PivotCaches.Create(SourceType:=xlExternal, SourceData:=Conn) _
                  .CreatePivotTable TableDestination:=Worksheets("ManHours").Range("A1"), TableName:="OnePager"
  
      ' create a new Pivot Table in "Pivot" sheet, start from Cell A1  ******* LINE BELOW PRODUCES AN ERROR ******
      Set PvtTbl = Worksheets("ManHours").PivotTables.Add(PivotCache:=PvtCache, TableDestination:=Worksheets("ManHours").Range("A1"), TableName:="OnePager")
  
  
      ' ******* LINE BELOW PRODUCES AN ERROR ******
      Set PvtTbl = PvtCache.CreatePivotTable(TableDestination:=Worksheets("ManHours").Range("A1"), TableName:="OnePager")
       'Create the headings and row and column orientation and all of your other settings here
  
  Else
      ' just refresh the Pivot cache with the updated Range (data in "ManHours" worksheet)
      PvtTbl.ChangePivotCache PvtCache  '&lt;--- ALSO PRODUCES AN ERROR !
      PvtTbl.RefreshTable
  End If
  
  End Sub
  </code></pre>