How to connect a SharePoint list to another SharePoint list using an OLE DB connection string?

The SharePoint Business Data List Connector BDLC can be used to pull data into a SharePoint list from another SharePoint list for any reason (e.g. from other site collection or installation).

You can use a connection string similar to this:

Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes; DATABASE=http://sharepoint.yourdomain.com/Documents/;LIST={5999B8A0-0C2F-4D4D-9C5A-D7B146E49698};

The select statements should look like this:

 SELECT * FROM listName

You can use the Item-IDs as primary keys for updating.

Remarks:

How to get the SharePoint list guid in browser?

Go to the list settings page. Right click on "Title, description and navigation" and copy the URL. Paste that into notepad and copy everything after "List=" in the string. That's your URL Encoded GUID for the list. All you need to do is decode it.

How to decode the SharePoint List guid (and any other encoded URL)?

You can use this page to decode encoded URLs:
http://www.albionresearch.com/misc/urlencode.php

READY TO GO NEXT STEPS?