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 = 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

Connection String

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

---

Attempted Solutions

• Disable Background Refresh - Already disabled
• Disable Auto-Recover (to save memory)
• Clear "Undo Stack" (to save memory)
• 'DoEvents' to delay code execution until each refresh is concluded, changing:

this

ActiveWorkbook.Connections("Connection_1").Refresh

to

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

SOLUTION!

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:

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

I don't know the specifics of why 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!

Lugger

Excel gets stuck on "Do While .Refreshing DoEvents Loop", with the query refreshing in the background endlessly. Any ideas why that could be happening? – Muniments

I'm not certain, but that piece of code features in one of my attempted solutions, not the one that actually ended up working for me. – Lugger

This has worked for me, thanks a lot. – Halimeda

Answer 1

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.

Nylon

I ran the code, and took a screen shot of each diablog box to compare it against subsequent ones - no changes. However, I still got the same errors. I'll have to step through the code tomorrow morning, but I'll report back before the bounty expires (sorry for the delay). – Lugger

@AaronContreras I've updated the code, if you want to try it out – Nylon

I'm still working through this, but the bounty has almost expired. I've awarded it to you because your answer has been the most comprehenisive in terms of debugging and troubleshooting. – Lugger

Very happy to report that your code, minus the dialogs, worked for me! Thank you so, so much! – Lugger

Answer 2

So I had a similar error when I was trying to create a VBA script to refresh an excel workbook automatically at a given time, and there were a few things that I did in my VBA script to get this to work. One of them being disabling background refresh. That could be your issue and you can easily do disable it by going to your connection properties and disabling background refreshing.

Here is what I did in VBA when I was getting this error, although I will say I was not using it with an MS access db. I had one excel workbook that I used as a 'runner' and it opened up the other books one by one and refreshed their connections. Basically I had a variable for the path and extension and put the names of each workbook into an array and looped through the array.

I combined the path and extension to give me the full filename of the file, you will see that in the loop.

This is what my loop looked like :

For i = LBound(testArray) To UBound(testArray)
    Dim wb As Workbook
    Set wb = Workbooks.Open(path & testArray(i) & ext, 0, False)

    'Next I checked to see if the workbook was in protected view and allowed for editing.
    If Application.ProtectedViewWindows.Count > 0 Then
        Application.ActiveProtectedViewWindow.Edit
    End If

    'Now comes the part that I believe should help for your case
    wb.Connections(testArray(i) & "This is your connection name").OLEDBConnection.BackgroundQuery = False
    wb.RefreshAll
    wb.Connections(testArray(i) & "This is your connection name").OLEDBConnection.BackgroundQuery = True

    wb.SaveAs fileName:= "Thisbook.xlsx"
    wb.Close
Next i

To get the connection name there are a few ways, including just looking to see what it is manually. For me because I wanted to make it so that I didn't need to manually put in every connection name I used the inherent pattern that I saw with the connections names.

In my case that was the baseNameOfWorkbook & " POS Report"

I do believe that you may be getting the errors due to background refreshing. So if you don't need to do this in VBA I would just suggest going to connection properties and disabling it.

Let me know if this works.

Balmung

I had a similar experience with similar results: enabling background refresh lets all of the PivotTable queries and rebuilds run at the same time. Since they can individually require a lot of memory, collectivly they are probably running out. – Cutlass

@Cutlass and the beauty about the VBA code was that I reinstated background refreshing at the end, so it really doesn't change anything in the workbook, just refreshes the connection. – Balmung

Unfortunately, the background refresh is already set to disabled, although I have it set manually through the data connection's properties, not through VBA. @Cutlass - is there a better way to cycle the memory other than with a full restart of the Excel application? – Lugger

@AaronContreras what do you mean you have it set manually through the data connection properties? the data connection? – Balmung

By manually, as opposed to setting that through VBA, I go into Data>Connections>Properties and in the Usage tab, simply uncheck "Enable background refresh". – Lugger

@AaronContreras So then if that still didn't help the issue, my last assumption would be, if your tables have a lot of data, that you are trying to refresh the tables too quickly. I am not sure if this is what you are doing, but I am under the impression that you open a book, refresh, save & close, then repeat. Or are all of these tables in a single workbook and you are trying to refresh all of them? – Balmung

From one Excel file, I have three data connections to seperate Access db's. I am refreshing each connection, one at a time. I don't think it's too fast, because with background refresh off, Excel physically prevents me from doing anything until the connection is refreshed. The third refresh always fails, but save/close fixes whatever the issue is, and allows the refresh. I'm only save/closing as a workaround, but I'd like to implement a better solution. Thanks, I hope that helps clarify! – Lugger

@AaronContreras you know Excel comes with a "Refresh All" button under the Data tab. Have you tried that? – Balmung

@Adjit, I have, sadly with the same result. I'm dealing with quite a large amount of data in these connections, and feel very confident that it is a memory problem. Any thoughts on clearing that memory, other than save/close? – Lugger

@AaronContreras so if you really think it is a memory problem then there are a few things you can do to give yourself some more memory. One option would be to turn off auto recovery as it takes up a decent amount of memory. Take a look here : add-ins.com/support/… – Balmung

@AaronContreras Can you provide more info about your data connections? OLEDB or ODBC? Are the Access files MDB's or ACCDB's? Do each of your queries connect to a different database, or do you use the same database for multiple queries? I doubt your problem is memory, it probably has to do with database connections. Maybe posting all your connection strings would help. – Nylon

@ExactaBox, good call, I've added one of my connection strings above. They're all identical, save which .accdb they connect to. I'm using OLEDB. Each connection is to a seperate database, there's no overlap. – Lugger

Answer 3

You can use VBA to call your refreshes individually via the activeworkbook.connections object. See this Stack Overflow post for some clues on this method. The more atomistic appoach may allow better insight and control. For example, once you have all the steps in place, you can try inserting DoEvents to solve the issue.

Mullion

I am calling them individually, and with Background Refresh disabled as your first link suggests. When this resulted in an error, I manually triggered the refresh of each connection through the prompts, with the same issues. I'm not having any trouble with the code concluding before the connections are completed, so I don't think the 'DoEvents' will clear it up. I will give it a whirl, though, and report back. – Lugger

I'm encouraged you have the calls organized this way. I suggest you edit your post to include the code. – Mullion

Answer 4

To clear system memory, you can always run something like this:

Sub ClearUndo()
Range("A1").Copy Range("A1")
End Sub

This will clear the undo stack which houses all the updates to your pivot tables, allowing you to undo them, if you do this in-between refershes it may help you keep memory usage in control.

Please dis-regard my previous suggestion as I was thinking of a solution that helped me in Access.

Gersham

Sorry @pegicity, I just got my code added. I'm not overly familiar with VBA, can you give me an example of how I would "set connection = nothing" in my code above? I'm not sure I have a connection object to dismiss... – Lugger

Yes that wouldn't help you, I wast thinking of using recordsets where ending the connection can free up memory, the undo stack can hold a huge amount of data, try using this suggestion to clear it all out – Gersham

Thanks for the update @Gersham - I threw that sub into Module 1, and called it after each "ActiveWorkbook.Connections("Connection_1").Refresh". Unfortunately, I still got the same errors on the third connection attempt :\ – Lugger

@AaronContreras hmm, did you try a manual refresh, running that sub in between? It could be where you are calling it in your code, oh the joys of version changes... – Gersham

Haha, no I hadn't tried that. I refreshed each manually, running the sub in between, but still got the error. The joys indeed :p – Lugger