For a while now, I've been trying to connect to SharePoint list data. The goal: read in data stored in SharePoint lists to use that data elsewhere, using SQL if possible (AutoTag has a wizard to help construct complex/long SQL statements).
So I go to my good friend Google and see what I can find. Bam! I get a return from ConnectionString.com. SharePoint lists? Check. SQL? Check.
So I chug along, writing a test application. It works!
I turn around and try a simple web part that does the same thing in SharePoint 3.0. I click on my test button and wait. And wait... and wait... And never get a response. Bewildered, I dig around the internet for more information. Not much comes up though.
So I ask the good people at Microsoft, and eventually get a response along the lines of:
The
ACE OleDb provider is not supported for accessing SharePoint data (it was
designed, implemented, and tested for linked lists in Access).
The theory as to why it does not work in the above case: the
provider is calling a web service to obtain the data. Attempting to do so
on the target server could result in "double hop" issues (code on the
server has issues making a web service request using the calling credentials).
Lesson: Do not use the OleDb provider if you need to access SP list data from inside of SharePoint. If you need to access the data from the server, use the SPList object. If you need to access the data from a client, use web services, the client object model, or REST.
Hope this saves someone time.
