SharePoint to Access without Linked Tables

[mjseim]

Greetings,

I'm wondering if anyone knows of a way to connect to a SharePoint list without using Linked Tables. I know... sounds wonky but I assure you I have good reason.

I've tried using a basic select query with this as my connection string:

Provider=Microsoft.ACE.OLEDB.12.0;WSS;HDR=NO;IMEX=2;ACCDB=YES;DATABASE=http://fake.domain.com/path/FinalFolder;LIST={655E1B5C-18E0-4C10-9ADB-B2FE2B2E2606};VIEW=;RetrieveIds=Yes

I've tried a pass-through query with the same connection string.

I've tried many variants of said connection string.

Then I got to thinking... if I can't link via a Select or PTQ, perhaps I could simply create an ODBC connection and leverage the data that way. Alas, I was still stumped since I was unable to figure out a way to connect to a SharePoint server via ODBC.

Am I out of luck? Am I forced to using linked tables?

Any thoughts are certainly appreciated.

 

[mjseim]

Wow... did I really stump Access World Forums! Just kidding, I love this place.


For what it's worth, I'm still stumped.

Moreover, it's getting worse. It would seem that using Data Connections in Excel to an Access Database that has SharePoint lists linked to it cannot refresh properly. The mere presence of the linked SharePoint list seems to make Excel barf.

To illustrate, I have Excel connected to a LOCAL TABLE in Access. I've ensured that the Mode=Read. The local table isn't in any way related to the SharePoint list linked in the same ACCDB. Excel won't refresh. It says the ACCDB is opened exclusively. I simple delete the reference to the SharePoint list in Access and Excel refreshes perfectly.

This is becoming a real problem for me. Truly, any advice out there?

 

[Jibbadiah]

Why not copy the file and then import or link? That way you don't interrupt other users and you aren't trying to view data sitting on a remote (and in my case often slow) sharepoint server. Use File System Object to copy sharepoint file to a local directory for use. Referring to a sharepoint file is just a simple reference to "http://sharepoint.location/file.txt"

 

[David232]

Not sure if possible but if you took the connection info that is used when connecting to the tables directly in Azure from SQL Enterprise Manager can you use those in your query string?
I have read an article on this but cannot find the link at the moment.
You use the connection info from the 'File' and then get the Read or Read/Write connection details and use these when making the SQL connection so cannot see whey this would not work directly in the query string! .