CODEWITHSUNDEEP
vbasharepointconnection-stringrecordset
Steeve
Steeve

Reputation: 23

Connection to two (or more) sharepoint lists with ADODB

I can connect to a SharePoint list with ADODB this way :

Dim objCon As New ADODB.Connection
objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=mysite/documents;LIST={xxxx-guid-xxxx};"

Dim rst As Recordset
Set rst = objCon.Execute("Select * from list1)

and it's working :).

Now, I would like to connect two list at the same time to do an inner join :

Set rst = objCon.Execute("Select * from list1 inner join list2)

but I don't find the syntax to put the second list GUID on the connection string. How can I do ?

Upvotes: 2

Views: 9993

Answers (3)

KurtL
KurtL

Reputation: 21

I know this is an older question but I was trying to attempt the same effort a while back and when I was looking for examples I generally saw the same answer of "No its not possible".

As Thomas G points out, it is possible as a 'disconnected recordset' (I typically refer to those as sub-queries) and then to join the two Lists together.

My goal is / was to avoid the need to first import the data into Excel just to run the queries with the Lists joined since I'm actually using it to bulk import several files per week into several Lists in SharePoint. I just don't want to deal with the data maintenance / clean up in Excel if I can avoid it in the first place (the several files are all using data from two different lists during the import).

In line with the original question, I've simplified the code to just retrieve the results of a Select query against two SharePoint Lists. The code is written for Excel VBA but can be used in any VBA instance if the Excel parts are removed.

Sub SQL_Two_SP_Lists()


    Dim sp_sdbPath As String, sp_sConnect As String
    Dim SP_List_1 As String, SP_List_2 As String
    Dim c As Long
    
    
    Dim cnSP As New ADODB.Connection
    Dim rsSP As New ADODB.Recordset
    
    
    sp_sdbPath = "https://your_SharePoint_URL_Here/"
    sp_sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & sp_sdbPath & ";"
    
    
    SP_List_1 = "LIST={List 1 GUID Here}" 'i.e. xxxx-guid-xxxx
    SP_List_2 = "LIST={List 2 GUID Here}" 'Go to List settings > Information Management Policy Settings > GUID is in the URL

    
    'Establish a connection to the first List
    cnSP = sp_sConnect + SP_List_1
    cnSP.Open

    
    'Write the SQL & Establish a connection to the second List as a sub-query using IN
    sSQL = "SELECT A.*, B.* " + _
           "FROM List A " + _
           "INNER JOIN (Select * From LIST IN 'DATABASE=" & sp_sdbPath & ";" & SP_List_2 & "' 'WSS;RetrieveIds=Yes;') B On A.Cust_ID  = B.Cust_ID;"
    
    rsSP.Open sSQL, cnSP, adOpenStatic, adLockReadOnly 'Change cursor & lock type if inserting, updating or deleting


    'The rest is to drop the results into an empty worksheet named 'Test'
    For c = 0 To rsSP.Fields.Count - 1    
        ThisWorkbook.Sheets("Test").Cells(1, c + 1) = rsSP.Fields(c).Name    
    Next
    
    ThisWorkbook.Sheets("Test").Cells(2, 1).CopyFromRecordset rsSP
    
    
    rsSP.Close
    cnSP.Close


End Sub

Upvotes: 1

RealTuffGuy
RealTuffGuy

Reputation: 1

The best way I have found to accomplish this is to use vb to import the tables to seperate sheets in excel. Then pull your sheet data into Power Query... join and manipulate in PQ. It is much faster than using vb to join seperate tables as listed above and looping thru each recordset. If you use Power query to grab the data directly from sharpoint instead of vb, it seems like each source in PQ actually pulls in All tables and then keeps the one you want - and it does this for every source table - too slow.

Upvotes: 0

Thomas G
Thomas G

Reputation: 10226

From Microsoft http://msdn.microsoft.com/en-us/library/ee633650.aspx

Selecting fields from multiple lists is not supported. You can create a dataset for each list and select fields from each dataset.

I don't know your environement but to me the easiest way to accomplish this would be to create 2 Linked tables pointing on your SharePoint lists and then you can manipulate their data the way you want.

Upvotes: 0

Related Questions