Access to SharePoint List via MS Access

[Oleg_Troyansky]

Hi everybody,

this time, I have a question, for a change... A client of mine has a SharePoint site with a few tables and an Access database, where the SharePoint tables are defined as Aliases. He developed a SQL query that loads one of the "local" Access Tables, joined with a few SharePoint tables.

The loads just fine from his local machine, but it fails when it's ran as a QVS task.

Our first stop was to compare security rights between the local machine and the server's account that runs the services. We could validate that the service account has full access to the SharePoint site and to the tables in question. It looks to me like all the bases are covered, and yet the SQL statement fails with an error "Cannot find Object ...".

Any insight would be highly appreciated.

Cheers,

Oleg Troyansky

[petter]

I have a strong hunch that you will have to have Access or an Access runtime installed on the server that is running the QVS task. I don't think the ODBC driver can handle the communication to SharePoint on it's own - it needs the help of Access (which is being used in the background) to pull it off.

[Oleg_Troyansky]

Thanks petter.skjolden‌! We'll check it out and report the results back! This is an interesting idea!

[petter · Accepted solution]

This passage from a blog seems to indicate that:

SharePoint Lists operates on ISAM model

Whenever we link to a ODBC data source, Access only needs to submit a SQL statement which is then executed on the server and sent back for Access’ consumption. However, when we query a SharePoint List for the first time, Access has to fetch all records and download them to a local cache in order to process the query which is then executed locally. In 2007, the local cache was stored in a XML format but with 2010, Access will use actual local Access tables (hidden from the UI) to work as a cache.

https : // accessexperts . com/blog/2011/07/07/sharepoint-lists-and-microsoftaccess/  

(remove some spaces from the above URL)

[Oleg_Troyansky]

Hi everybody,

so, here is an update for this problem, that remains open unfortunately:

- We verified that the Server and the Services User has full access to the SharePoint site

- We installed MS Access on the Server, to ensure that any "heavy lifting" that needs to be done in Access, is covered.

- We installed QlikView Developer on the Server, to verify how the script runs in the manual mode.

And the results are:

- the script is running just fine when it's running manually from the Developer tool, under the same Services User ID, on the same Server.

- the script is able to establish the ODBC connection to the MS Access Database and to load data from Access, both manually and running as a QVS task.

- The same script fails to access SharePoint tables via Access, when it's running as a QVS task.

So, we are back at the drawing board. Qlik support suggested that it's a matter of SharePoint permissions to pass user credentials directly. If this is the case, we don't know where to look for this setting.

Any additional help is highly appreciated.

cheers,

Oleg Troyansky

[petter]

This solution is accessing SharePoint lists which are linked from Access - right?

Would it be an option instead to get the SharePoint list data directly to QlikView without using Access and ODBC as an intermediary?

This last option has been tested and works - also on the server side. It will probably be much more efficient and will be cleaner....

It can be done with one of at least two API's of SharePoint. The pre REST version that is using GET requests - kind of similar to REST. And the more modern full RESTful version. 

[Oleg_Troyansky]

Hi Petter,

thank you for your response. Accessing SharePoint directly would be less convenient - the SharePoint data is used as Master tables for the transactional data stored in MS Access, so the JOIN query that combines both the Access table and a handful of SharePoint tables would be the best in this situation. The alternative is loading the 6 tables individually and then performing 5 JOIN loads. As a workaround it would be "an OK" solution if nothing else works.

Could you please point me at a white paper or another document describing how the direct access to SharePoint should work?

thanks, appreciate your help!

Oleg Troyansky

[petter]

Which version of SharePoint it it? I presume SharePoint 2013 for the following:

A super simple but low level approach is to use SharePoint's REST API with regular LOAD ... FROM [http://....].

The service account that the load script runs as has to have proper access to SharePoint for the data that is requested.

Then there is no need for userid/password since "Windows Integrated Security" will handle it.

As long as you are not updating back to SharePoint this simple plain-old-fashioned LOAD will work as it is a GET request that is sent to SharePoint. You don't need any REST connector or anything else...

The URL you give when using the Load Script Wizard for Web File is:

    http: // <site url>/_api/web/lists/GetByTitle(‘Test')/items               ( remove spaces on each side of // )

For my SharePoint Foundation 2013 this URL works since it is a standard SharePoint list in any SharePoint 2013 installation:

    http: // win-3su85ag3k0g:26796/_api/web/lists/GetByTitle('Health%20Analyzer%20Rule%20Definitions')/items

          ( remove spaces on each side of // )

The win-3su85ag3k0g is the computer name/hostname

26796 is the port number that SharePoint Administration Service is running on - will be randomly chosen by SharePoint installation and thus vary between computers.

Have a look at the attached QlikView app - replace the URL accordingly in the load script.

[petter]

Here is the MSDN article where it is documented:

     https: // msdn.microsoft.com/en-us/library/office/dn292552.aspx

[petter]

This might be of interest too:

How to load data from Sharepoint 2013 from QlikView and Windows 2012?

There are numerous articles/discussions here on Community discussing and proposing solutions.

I have also used CURL.EXE (FreeWare command utility) that gives full access to both READ and WRITE for SharePoint by using GET and POST requests. This utility also handles integrated security well. If you need an example app using CURL I could provide it ... let me know.