none
ADO Connection to Sharepoint list issue RRS feed

  • Question

  • I am trying to use MS Word to connect to a Sharepoint Online List using Microsoft.ACE.OLEDB.12.0 as my provider.

    I can get it working with my in house Sharepoint 2013 but not with Sharepoint 2013 Online.

    I am however able to accomplish the connection using MS Access to both the in house and online Sharepoint.  I am using the same references in Access and Word but the Word VBA is giving me a run time error....Operation canceled by user.

    Does anyone have a clue why I am getting this error?  Or how to get the connection to the online Sharepoint working?

    Friday, March 21, 2014 4:56 PM

Answers

  • I didn't get much further. I can't replicate your problem here as I don't have access to an online list, but here is something I noticed using an in-house SP2013 list.

    I have a little bit of test code like this, in Windows 7/Word 2010, with a reference to the Microsoft ADO 6.1 library 

    Sub getsplist()
    Dim c As ADODB.Connection
    Dim r As ADODB.Recordset
    Set c = CreateObject("ADODB.Connection")
    c.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended properties=""WSS;HDR=NO;IMEX=2;DATABASE=http://192.168.235.114/sites/sc1;LIST={E7A0B80D-B72E-4F39-9AB5-571D342DD63A};TABLE=test list 1;"";"
    c.Open
    Set r = New ADODB.Recordset
    r.Open "SELECT L.* FROM [test list 1] L", c

    ' I have a breakpoint here to let me look at the RecordSet r

    r.Close

    Set r = Nothing

    c.Close
    Set c = Nothing
    End Sub

    That works OK. But the connection string I got from Access used this:

    ACEWSS;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=http://192.168.235.114/sites/sc1;LIST={E7A0B80D-B72E-4F39-9AB5-571D342DD63A};VIEW=;RetrieveIds=Yes;TABLE=test list 1

    Changing "ACEWSS" to "WSS" appears to be enough to make everything work here. Otherwise, the code either flags a permissions problem or a problem with a double-quote character. "ACEWSS" and "WSS" are probably two different Jet/ACE IISAMs - kind of "sub-providers" of the main Jet/ACE provider, and it is possible that "ACEWSS" is (a) required to deal with some aspects of SP 2013 and (b) more picky in terms of syntax. Documentation of these IISAMs has never been particularly easy to find, suggesting that Microsoft really regards them as internal interfaces for use by Access and not something for "general use".


    Peter Jamieson


    Tuesday, March 25, 2014 7:47 PM

All replies

  • Hi,

    Welcome to MSDN forum.

    SharePoint online is one of Microsoft online services.

    The question is more related to SharePoint online, I would recommend you to repost the question on Microsoft Online: SharePoint Online forum or Office 365 forum.

    Hope this helps.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 25, 2014 1:31 AM
    Moderator
  • I know you said "ADO" but do you mean that you are using the ADODB library, or are you trying to use an ADO connection string from, e.g. Word's InsertDatabase or OpenDataSource methods? If the latter, there are likely to be problems unless you use an external .odc file.

    Peter Jamieson

    Tuesday, March 25, 2014 3:34 PM
  • I'm trying to create a new ADODB.Connection with Provider=Microsoft.ACE.OLEDB.12.0 if that helps any.  I'm not using InsertDatabase or OpenDataSource methods.
    Tuesday, March 25, 2014 4:29 PM
  • I didn't get much further. I can't replicate your problem here as I don't have access to an online list, but here is something I noticed using an in-house SP2013 list.

    I have a little bit of test code like this, in Windows 7/Word 2010, with a reference to the Microsoft ADO 6.1 library 

    Sub getsplist()
    Dim c As ADODB.Connection
    Dim r As ADODB.Recordset
    Set c = CreateObject("ADODB.Connection")
    c.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended properties=""WSS;HDR=NO;IMEX=2;DATABASE=http://192.168.235.114/sites/sc1;LIST={E7A0B80D-B72E-4F39-9AB5-571D342DD63A};TABLE=test list 1;"";"
    c.Open
    Set r = New ADODB.Recordset
    r.Open "SELECT L.* FROM [test list 1] L", c

    ' I have a breakpoint here to let me look at the RecordSet r

    r.Close

    Set r = Nothing

    c.Close
    Set c = Nothing
    End Sub

    That works OK. But the connection string I got from Access used this:

    ACEWSS;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=http://192.168.235.114/sites/sc1;LIST={E7A0B80D-B72E-4F39-9AB5-571D342DD63A};VIEW=;RetrieveIds=Yes;TABLE=test list 1

    Changing "ACEWSS" to "WSS" appears to be enough to make everything work here. Otherwise, the code either flags a permissions problem or a problem with a double-quote character. "ACEWSS" and "WSS" are probably two different Jet/ACE IISAMs - kind of "sub-providers" of the main Jet/ACE provider, and it is possible that "ACEWSS" is (a) required to deal with some aspects of SP 2013 and (b) more picky in terms of syntax. Documentation of these IISAMs has never been particularly easy to find, suggesting that Microsoft really regards them as internal interfaces for use by Access and not something for "general use".


    Peter Jamieson


    Tuesday, March 25, 2014 7:47 PM