title: Excel Data Connection errors on Refresh
body: <p><strong>Solved! See below for solution!</strong></p>
  
  <p>I'm in Excel 2010 connecting to multiple, <strong>seperate</strong> Access 2010 db's from Excel through PivotTable data connections.</p>
  
  <p>Refreshing all my connections causes the final refresh to fail. The order does not matter, I've manually refreshed in different orders, same error.</p>
  
  <p>However, if I save and close after refreshing a few, then come back and refresh the last one, there is no problem at all.</p>
  
  <p>Leads me to believe that I'm hitting some sort of memory cap that is reset when I save and close.</p>
  
  <p>Can I re-create that effect through VBA <strong>without actually save/closing?</strong> Is there a better solution to this issue?</p>
  
  <p><strong>Error Messages</strong> - These three pop up in this order:</p>
  
  <ul>
  <li>The query did not run, or the database table could not be opened.</li>
  <li>Problems obtaining data.</li>
  <li>A PivotTable, cube function or slicer using connection has failed to refresh.</li>
  </ul>
  
  <p><strong>Current Code</strong></p>
  
  <pre><code>Private Sub CommandButton1_Click()
  On Error GoTo ErrHndlr
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.StatusBar = "Refreshing Data - Please Be Patient"
  
  ActiveWorkbook.Connections("Connection_1").Refresh
  ActiveWorkbook.Connections("Connection_2").Refresh
  ActiveWorkbook.Connections("Connection_3").Refresh
  
  Application.Calculation = xlCalculationAutomatic
  Application.StatusBar = "Ready"
  [LastUpdated].Value = FormatDateTime(Now, vbGeneralDate)
  Application.ScreenUpdating = True
  Exit Sub
  
  ErrHndlr:
    Application.StatusBar = "Ready"
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    [LastUpdated].Value = "Update Error"
    Exit Sub
  
  End Sub
  </code></pre>
  
  <p><strong>Connection String</strong></p>
  
  <pre><code>Provider=Microsoft.ACE.OLEDB.12.0
  ;User ID=Admin
  ;Data Source=C:\Folders\Database_1.accdb
  ;Mode=Share Deny None
  ;Extended Properties=""
  ;Jet OLEDB:System database=""
  ;Jet OLEDB:Registry Path=""
  ;Jet OLEDB:Engine Type=6
  ;Jet OLEDB:Database Locking Mode=0
  ;Jet OLEDB:Global 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=False
  ;Jet OLEDB:SFP=False
  ;Jet OLEDB:Support Complex Data=False
  ;Jet OLEDB:Bypass UserInfo Validation=False
  </code></pre>
  
  <hr>
  
  <p><strong>Attempted Solutions</strong></p>
  
  <ul>
  <li><em>Disable Background Refresh</em> - Already disabled</li>
  <li>Disable Auto-Recover (to save memory)</li>
  <li>Clear "Undo Stack" (to save memory)</li>
  <li>'DoEvents' to delay code execution until each refresh is concluded, changing:</li>
  </ul>
  
  <p>this</p>
  
  <pre><code>ActiveWorkbook.Connections("Connection_1").Refresh
  </code></pre>
  
  <p>to</p>
  
  <pre><code>With ActiveWorkbook.Connections("Connection_1")
    Select Case .Type
      Case xlConnectionTypeODBC
        With .ODBCConnection
          .Refresh
          Do While .Refreshing
            DoEvents
          Loop
        End With
      Case xlConnectionTypeOLEDB
        With .OLEDBConnection
          .Refresh
          Do While .Refreshing
            DoEvents
          Loop
        End With
      Case Else
        .Refresh
    End Select
  End With
  </code></pre>
  
  <p><strong>SOLUTION!</strong></p>
  
  <p>Side note, I have a couple extra connections that I didn't want updated through this code, and added some additional, simple logic to specify which connections I wanted updated. This code here works to refresh every connection in your workbook:</p>
  
  <pre><code>Dim i As Integer
  Dim awc As WorkbookConnection
  Dim c As OLEDBConnection
  
  Set awc = ActiveWorkbook.Connections.Item(i)
  Set c = awc.OLEDBConnection
  c.EnableRefresh = True
  c.BackgroundQuery = False
  c.Reconnect
  c.Refresh
  awc.Refresh
  c.MaintainConnection = False
  Next i
  </code></pre>
  
  <p>I don't know the specifics of <em>why</em> this works, which part of this allows Excel to get over its self-limitation. I'd love to hear more if anyone is more familiar!</p>