Wednesday, January 27, 2010

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!

Thursday, January 14, 2010

Don't Forget to "DoEvents"

I'm writing a reporting package which uses Microsoft Access and Microsoft Excel to gather and chart data collected from a set of custom SharePoint lists.  I have some VBA code which does some calculations on all of the list items before the reports are generated to ensure that the data is the most up-to-date that it can be.

The VBA code lives in Access.  It basically does a lookup on each row in one of my lists and sets values in another list.  The business rules for this update had enough conditions on it that I couldn't implement it in one complex Update statement, thus the looping.

I also discovered that you can very easily add a progress bar to the Access status bar by calling "SysCmd acSysCmdInitMeter, 'message', maximum value" to initialize it, and "SysCmd acSysCmdUpdateMeter, currentValue" to increment the counter.  (To reset the status bar when I'm done, I call "SysCmd acSysCmdRemoveMeter".)  So I added this bit of functionality to the VBA code as a bonus for my users to give them some feedback.  After all, some of the lists I'm working with have a few thousand items in them (don't get me started about best practices stating that 2000 items is the workable maximum—I've already fought that battle and lost!) and it can take some time for the VBA code to complete.

Well wouldn't you know it, but the update code continually ran, and the thread blocked on the SQL command, so the progress bar just sat there, no progressing.  Furthermore, the whole UI appeared to be locked up while the update was running.  I couldn't even pause execution of the VBA code because the IDE wouldn't recognize my mouse clicks on the pause button.

And then I remembered something from my old VB6 days: the DoEvents method.

The DoEvents method relinquishes control of the processor by the VBA engine to allow other processes to do things. Or more accurately in my case, other threads. It was more important to call it when a background thread was doing heavy processing and you wanted to allow the UI thread a chance to handle the mouse clicks, keyboard presses, and other events which had queued up since the last time it got a time slice from the operating system.

I remembered one main warning about using it, though:

  • You don't want to call it too often, since an expensive context switch most likely would take place

So I decided to see if it worked in VBA by adding this block of code:

If currentRow Mod 10 = 0 Then
    DoEvents
End If

In this block of code, currentRow is an integer which gets incremented each time through the loop. It is the variable that I pass to the method which updates the progress bar. You can see that I don't call DoEvents each time through the loop—I want the main processing code to actually finish in a reasonable amount of time, after all!

The IDE didn't complain about the method call, so it at least recognized it as valid VBA code.  So far, so good. But will it accomplish what I want it to?

The short answer: YES!

So now, in the latest version of the code, the DoEvents method is called every 10 rows.  This allows the UI to get refreshed, so I can now pause execution and scroll through the source code (albeit in bursts).

But more importantly, the progress bar now shows progress as the code runs!

DoEvents FTW!

Friday, January 8, 2010

SharePoint Exception Occurred 0x80020009 (DISP_E_EXCEPTION) – UPDATE

A colleague pointed me to the following article: Description of the Windows SharePoint Services 3.0 Cumulative Update Server Hotfix Package (Sts.msp): June 30, 2009.  In it, it makes reference to the DISP_E_EXCEPTION error.  Specifically, it states:

SPList.GetItems(SPQuery) fails when the item count reaches the threshold value 2000, and you will receive the following error message:

Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))

Presumably, if you have a custom query on a list, and that query returns more than 2000 items (which is the case on the pages where I'm encountering this error), you'll get this exception.  The fix for this, supposedly, is in the June 30, 2009 hotfix package.

I'm in the process of requesting that this hotfix be applied to our SharePoint server.  I'll post again once that happens and let you all know whether it fixes this problem as advertised.

While searching for possible causes for this exception, the blog posts I found mentioned improperly-configured load balancers and other maladies, but not this 2000-item limitation nor the hotfix.  Hopefully this post will help someone else out there who is having a similar problem on their system.  Please comment if that person is you!

Thursday, January 7, 2010

Best Practices for Building SharePoint Solutions

A friend forwarded this link to me: 10 Best Practices For Building SharePoint Solutions.  Most are fairly obvious, but sometimes it's good to state the obvious things, lest we forget them.

Monday, January 4, 2010

Performing Outer Joins on a SharePoint List from Access

I'm currently creating reports from the custom SharePoint lists I have been working on over the past few months.  The way I'm accessing the data in my SharePoint lists is through Access 2007, and then I have an Excel 2007 spreadsheet which uses the Access database to get the data. In this manner, I can use the power of Access' database engine to perform queries against my lists, and then just import the data to Excel.  It sounds cumbersome, and perhaps I'm showing some of my ignorance of Access and Excel development, but it runs much faster than the earlier version of the report which uses VBA only to generate similar reports.

That being said, I ran into an issue with one query.  I have a list which tracks FCC filings.  There can be a major filing and a minor filing, both tied to the same physical location.  So there can be one or two filings (items) per location stored in this list.  I needed to report on whether the filings were "complete".  They are considered complete if the major filing is complete, or if the minor filing is complete in the case where a location does not have a major filing.

My SQL statement was structured like this:

SELECT * FROM
(
    SELECT <<completed major filings>>
    FROM Filings
    WHERE Filings.Type = 'Major'
UNION
    SELECT <<completed minor filings>>
    FROM Filings Minor
    LEFT JOIN Filings Major ON Minor.Location = Major.Location
    WHERE Minor.Type = 'Minor'
    AND Major.Type = 'Major'
)

However, this didn't work.  The second part of the query kept behaving like an inner join.  I did some proofs-of-concept to discover that, yes, outer joins do work in Access on actual tables within Access, but they just weren't working on linked tables where the source is SharePoint.

What to do?

After beating my head against the wall for a while, and possibly pulling some hair out, I finally had the bright idea to break things into smaller chunks.  Thinking that Access had a problem joining a linked table with itself, I created an Access query which only returned items representing minor filings, and another Access query which only returned items representing major filings.  Then I removed the where clauses from my original SQL statement above and replaced direct access to the linked table with the queries I had created.

It worked beautifully!

The new SQL Statement looks like this:

SELECT * FROM
(
    SELECT <<completed major filings>>
    FROM AllMajorFilings
UNION
    SELECT <<completed minor filings>>
    FROM AllMinorFilings Minor
    LEFT JOIN AllMajorFilings Major ON Minor.Location = Major.Location
)

Lesson learned: when working with Access, break things into smaller components to avoid confusing Access.

I can't wait to get back to "normal" C# development!