vba 使用 ADODB 连接到两个（或更多）共享点列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26651642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connection to two (or more) sharepoint lists with ADODB
提问by Steeve
I can connect to a SharePoint list with ADODB this way :
我可以通过这种方式使用 ADODB 连接到 SharePoint 列表：
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 ?
但我没有找到将第二个列表 GUID 放在连接字符串上的语法。我能怎么做 ？
采纳答案by Thomas G
From Microsoft http://msdn.microsoft.com/en-us/library/ee633650.aspx
来自微软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.
我不知道您的环境，但对我而言，完成此操作的最简单方法是创建 2 个指向您的 SharePoint 列表的链接表，然后您可以按照自己的方式操作它们的数据。
回答by KurtL
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.
正如 Thomas G 指出的那样，有可能作为“断开的记录集”（我通常将它们称为子查询）然后将两个列表连接在一起。
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)
我的目标是/是为了避免需要首先将数据导入 Excel 只是为了在加入列表的情况下运行查询，因为我实际上使用它每周将多个文件批量导入 SharePoint 中的多个列表。如果我可以首先避免它，我只是不想处理 Excel 中的数据维护/清理。（这几个文件在导入过程中都使用了来自两个不同列表的数据）
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.
根据原始问题，我已简化代码以仅检索针对两个 SharePoint 列表的 Select 查询的结果。该代码是为 Excel VBA 编写的，但如果删除了 Excel 部件，则可以在任何 VBA 实例中使用。
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
结束子
