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.
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!