Error While Refreshing External Data Source in Excel

I have an Excel 2007 workbook that pulls its data from an Access 2007 database to give users some feedback via charts and graphs.  [For this discussion it doesn't matter that Access actually gets the data via links to lists on a SharePoint site—the error I encountered deals with the connection between Excel and Access.]

I added an "Update" button that the user presses to ensure that all of the data is freshly pulled from Access.  Initially I just called

ThisWorkbook.RefreshAll

However, I received an error at some point that I couldn't pinpoint.  I have about 150 different queries that pull data from Access, and trying to find the one query that was failing was like finding the proverbial needle in a haystack.

So I changed the code to loop though all of the named ranges in the workbook.  I had given all of the data tables names when developing the spreadsheet, and so the new code looks like:

for each name in ThisWorkbook.Names
   name.RefersToRange.ListObject.QueryTable.Refresh
next

[Yes, I check for whether the name object actually refers to a range, and if so, whether that range has a List Object, and if so, whether that List Object has a Query Table to refresh!]

This way I could tell which range was being refreshed and could inform the developer (me) of which range was causing the problem. I could also calculate a percent complete value to display on the status bar, since I knew how many names there were and which name I was currently working on (via a "currentName" variable).

This looked promising, but I again got an error.  Looking at the named range that the code was refreshing when the error occurred, I didn't see anything wrong with it.  I could manually refresh it with no problem.

So I started looking at the properties of the QueryTable object to see if there were any clues in there.  That's when I stumbled upon the "MaintainConnection" property.

I changed the code to this, to see if it made any difference:

for each n in ThisWorkbook.Names
n.RefersToRange.ListObject.QueryTable.MaintainConnection = False
n.RefersToRange.ListObject.QueryTable.Refresh
next

This time when I ran it, it ran to completion without any errors.

So it seems that if you try to maintain your connection to the database, you'll eventually run out of available connections.  What's strange is that the searches I did came up with information about Excel handling up to 255 connections, and I seemed to bump into this problem at about connection number 60 or so.  I'm not sure of the exact count—it could have been more or less than 60, but it was pretty close, I believe.

So if you're attempting this on your own, take it from me: set the MaintainConnection property to False!