Cannot connect to SharePoint site using ADODB from excel

Question

I would like to be able to create an adodb recordset in excel that I can loop through making addition/edits etc as necessary. The following creates the recordset but throws the error (80004005) "Cannot connect to SharePoint site. Try again later" on the last line:

    strLURL = "<SharePoint list URL>"
    strGUID = "{D16F31E9-41F3-4193-8926-35FFC0714C86}"
    intIMEX = 1
    strList = "Planner"
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;"
    strConn = strConn & "IMEX=" & intIMEX & ";"
    strConn = strConn & "RetrieveIds=Yes;"
    strConn = strConn & "DATABASE=" & strLURL & ";"
    strConn = strConn & "LIST=" & strGUID & ";"
    strConn = strConn & "VIEW=; RetrieveIds=Yes;"
    strConn = strConn & "Table = " & strList

    Dim cnCur As New ADODB.Connection
    Dim rsCur As New ADODB.Recordset
    Dim strSQL As String

    cnCur.Open ConnectionString:=strConn
    strSQL = "SELECT * FROM [" & strList & "]"
    rsCur.Open Source:=strSQL, ActiveConnection:=cnCur

Answer 1

I was unable to find a solution using an ADODB connection. Instead I used the methods for working with listobjects - which are quite straightforward:

'Declare variables
Dim lo as ListObject
Dim ws as Worksheet
Dim sServerName As String 'site address e.g "http://yoursite.com/_vti_bin/"
Dim sName As String 'list id e.g {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
Dim sGUID As String 'view GUID e.g {yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy}


'Create listobject
Set lo = ws.ListObjects.Add( _
       SourceType:=xlSrcExternal, _
       Source:=Array(sServerName, sName, sGUID), _
       Destination:=Range("A1") _
        )

'Do something with your listobject here

'Push updates back to server
lo.UpdateChanges (xlListConflictDialog)

Answer 2

While it is true that you should not access the SQL Server database directly, the technique described in the question does not violate this rule. The technique described uses a SQL-like API to query the list. You'll notice that the query doesn't reference an actual SQL Server table.