Excel Data Connection errors on Refresh

Question

Solved! See below for solution! I'm in Excel 2010 connecting to multiple, seperate Access 2010 db's from Excel through PivotTable data connections. Refreshing all my connections causes the final refresh to fail. The order does not matter, I've manually refreshed in different orders, same error. However, if I save and close after refreshing a few, then come back and refresh the last one, there is no problem at all. Leads me to believe that I'm hitting some sort of memory cap that is reset when I save and close. Can I re-create that effect through VBA without actually save/closing? Is there a better solution to this issue? Error Messages - These three pop up in this order: - The query did not run, or the database table could not be opened. - Problems obtaining data. - A PivotTable, cube function or slicer using connection has failed to refresh. Current Code ``` Private Sub CommandButton1_Click() On Error GoTo ErrHndlr Application.ScreenUpdating = False Application.Calculation = xlCalcul…

Accepted Answer

This is not a full answer, but an attempt to help debug, so that hopefully we can arrive at a solution.

I believe you can solve this issue by debugging the Connections. Try replacing your Refresh code above (and the replacement with DoEvents) with the following Sub. First, it is possible that displaying the dialog between Refreshes will fix the problem (if the problem is concurrent refreshes, etc). Second, each time it runs, check carefully that nothing has changed. Please report back with any discoveries or info. If you still get the errors, step through the code and report back the line that raises the error.

```
Sub ShowDebugDialog()

Dim x As Integer
   Dim i As Integer, j As Integer
   Dim awc As WorkbookConnection
   Dim c As OLEDBConnection

For i = 1 To ActiveWorkbook.Connections.Count
   'For i = ActiveWorkbook.Connections.Count To 1 Step -1

For j = 1 To ActiveWorkbook.Connections.Count
         Set awc = ActiveWorkbook.Connections.Item(j)
         Set c = awc.OLEDBConnection
         x = MsgBox("ConnectionName: " & awc.Name & vbCrLf & _
              "IsConnected: " & c.IsConnected & vbCrLf & _
              "BackgroundQuery: " & c.BackgroundQuery & vbCrLf & _
              "MaintainConnection: " & c.MaintainConnection & vbCrLf & _
              "RobustConnect: " & c.RobustConnect & vbCrLf & _
              "RefreshPeriod: " & c.RefreshPeriod & vbCrLf & _
              "Refreshing: " & c.Refreshing & vbCrLf & _
              "EnableRefresh: " & c.EnableRefresh & vbCrLf & _
              "Application: " & c.Application & vbCrLf & _
              "UseLocalConnection: " & c.UseLocalConnection _
              , vbOKOnly, "Debugging")
      Next j

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

End Sub
```

Additional questions you can answer if you're still getting errors:

- Was BackgroundQuery always false?

- Was there a perceptable delay between each set of dialogs (indicating Excel is waiting for a refresh to complete) or did they all come up immediately after the last one?

- Which line of code raises the initial error? If you refresh the Connections in backwards order (by uncommenting the "Step -1" line) do you get the error at the same connection?

- When you say you can update the connections manually, is this through a different macro or through Data >> Connections >> Refresh?

- Any errors if you manually select "RefreshAll"?

Sorry for all the questions but you have to think of everything when debugging nasty connection errors like this.

Excel Data Connection errors on Refresh

Solution

Problem