Using Access 2007 to Migrate SharePoint Content

In my career, Microsoft Access has really played one and only one role: the legacy system we frantically had to get our client far far away from. Aside from one nightmarish development experience writing an application in it, Access has been that *** database that one person in the firm can’t live without and each and every developer / DBA / administrator refuses to touch it.

So as I’ve been getting comfortable with Office 2007, I noticed that Access 2007 was indeed still a major player. I was most miffed when I saw that it got a ribbon, and my baby, InfoPath, did not! But I got over it, closed Access, and went on with my life.

Going on with my life, however, came to a screeching halt shortly after I completed my firm’s SharePoint migration from 2003 to 2007. The problem was that some of the lists we moved over got a little screwy. See my last blog entry for details. Furthermore, our 2007 site made use of my Hybrid Provider, which, at the time, did not have datasheet views of lists or client integration (most notably the Excel exportation feature) set up correctly.

And since no one felt like manually rebuilding the list, we needed a slick way to move data over. As a developer, anything “slick” that I’ve done has been the result of writing code. So I spent (or perhaps more accurately wasted) a lot of time trying to come up with the end-all-be-all SharePoint 2007 list migration tool.

Well, it really sucked; it didn’t work; I don’t want to talk about it.

That gets us fairly late into the last evening I had allotted to this task before absolutely having to throw in the towel and come up with something else. I was sitting in front my computer with a beer in one hand and my head in the other, fighting the thought of Googling some utility I could buy (which, for a developer, is of course tantamount to your wife suggesting to call a plumber when you just know that you are perfectly capable of fixing that leaky sink).

And then I remembered one brief little comment my boss made to me some time ago about Access 2007. It was along the lines of “Yeah, you can pretty much connect to or from anything with the new Access.”

“Anything?” I suddenly thought. So I fired it up, and after a few seconds of poking around the ribbon, I wandered onto the “External” data tab. And sitting right there in front of me, as if by some magic or divine intervention, was a button that claimed to do everything I’ve been toiling around with for the last week.

Import SharePoint List.

And sure enough, in the next block over, was the other half of the battle:

Export To SharePoint List.

Holy crap! That was it! Here is a quick method of how to use Access 2007 as a conduit between SharePoint environments.

1. Background: I needed to migrate a list from an externally hosted 2003 site to our newly created 2007 site.  Both sites have the same URL; we use a third party DNS tool to point to which ever server we want public.
2. Open the hosts file on your machine (C:\Windows\System32\drivers\etc) and make an entry to point the 2003 box.
3. Open Access 2007, click on the “External Data” tab, and select “Import SharePoint List.”
4. A wizard comes up.  Basically, all you need to do it point the URL to a SharePoint site on the 2003 box, pick a list, and pick a view.
5. Finish the wizard.  There a few Office 2007-ish features you can select.  When you’re done, Access will dump the list data into a new database table.
6. Redirect your machine to the 2007 box via the hosts file.
7. (Only if using the HybridProvider or another customer authentication mechanism.)  Put the web app you are migrating the list to into Windows authentication mode.  I’m still working on getting client integration working with my HybridProvider; for now, Windows auth is just easier, and switching between the two is a matter of a radio button and a textbox.
8. Back in Access, click “Export To SharePoint List.”  This will basically take you backwards through the previous wizard, selecting a URL to a SharePoint list to create / populate from the current database table.  That’s it!

This actually works remarkably well. You might have to get creative with using views to shape the data (for example, to bring in all the list columns). In our situation, one of the columns was a lookup into user info, which won’t work since the migration took us across different domains. So what I had to do was convert the lookup column to a text column, and bring the data over that way. Also, keep an eye out for meta data (especially dates) getting a little screwy. Have fun!