Forum Discussion
reading access databases hosted in sharepoint libraries: works in desktop, fails on service
Thank you for the suggestions lbendlin
Still gives the same error with both changes.
I don't think the error is at "read sharepoint" but rather when it does the
Access.Database()
eg this query refreshes correctly from the service:
let
Source = SharePoint.Contents("https://nsrltd.sharepoint.com/sites/DataManagement", [ApiVersion = 14]),
Documents = Source{[Name="Documents"]}[Content]
in
Documents
I can even read an excel via the service with:
let
Source = SharePoint.Contents("https://nsrltd.sharepoint.com/sites/DataManagement", [ApiVersion = 14]),
Documents = Source{[Name="Documents"]}[Content],
#"Tables_demo xlsx" = Documents{[Name="Tables_demo.xlsx"]}[Content],
#"Imported Excel" = Excel.Workbook(#"Tables_demo xlsx"),
RawData_Sheet = #"Imported Excel"{[Item="RawData",Kind="Sheet"]}[Data]
in
RawData_Sheet
But fails on access databases (refreshing from service.. works fine in desktop )
Edit: tried reducing the failing query a bit more
let
Source = SharePoint.Contents("https://nsrltd.sharepoint.com/sites/DataManagement", [ApiVersion = 14]),
Documents = Source{[Name="Documents"]}[Content],
#"Gateway_test accdb" = Documents{[Name="Gateway_test.accdb"]}[Content],
#"Imported Access" = Access.Database(#"Gateway_test accdb")
in
#"Imported Access"
This also fails on the service but works on PBI desktop. (only opens the database and list tables rather than trying to read rows from a table).
Super UserAh, I totally missed the point that you are still using Access. (Why, though?)
Remember that Access creates an .ldb file (locking semaphore) when opening the database. That's not something the service can do on a sharepoint.
Hi lbendlin ...
Users are supplied data in access format (complex database dump from remote system).
Again, desktop power BI is able to open the file so I would assume sharepoint authorised by the oauth2 credentials should do the same for any connection (that can establish the connection to the database). We have had a similar issue in the past with access databases hosted in a local file system. For those we needed to install the accessengine components on the server where the on-premise gateway was running. In this case it doesn't use a gateway that we can control (cloud to cloud) and we can't force an access via our gateway since the gateway sources don't support the oauth2 authentication that sharepoint appears to require. (I can create an on-premise gateway connection, but without oauth2, it always fails authentication on the connection. If I tell it to ignore the conenction test and try to map anyway, it fails when it trys to connect later).
In the thread I accidently necro'ed the poster had the same issue but worked around by exporting to a sharepoint list (our data is both too large/complex for that and used by other business processes in that format)
If Access.Database() isn't supported for sharepoint content we should get it documented (or better, fixed). It does pass the folder reference into the functions so - maybe a missing protocol, or given our experience with the file system hsoted version, it possibly passes the responsibility for connection to the source server (sharepoint host) which doesn't have the MDAC/accessengine modules available to it).
No one else uses this type of connection? I was hoping it was just me missing a step or something (works for desktop, but for the service you have to add xyz step as well).
Our IT group are pushing all teams to move all files to sharepoint libraries for better version control and tracking hence the requirement.
Thanks for time and attention. And any other suggestions any one might be willing to share!
Jen
- lbendlin
Is there any chance in your reality to dump Access and move to an enterprise database like SQL server?
lbendlin wrote:Is there any chance in your reality to dump Access and move to an enterprise database like SQL server?
*grin* we have many sql instances (large mining company) and connect to them in all sorts of interesting ways.
We also use sharepoint lists and libraries, automate, azure and various cloud data storage systems.
But this one (and others) are shared with us as access databases. And it works from PBI desktop. And PBI service to file system via on-premise gateways as long as the gateway server is configured with the accessengine install. I could SSIS it to a SQL instance but that is another process to maintain, and every chance this is just a problem with the way I coded the database access (just via the GUI, no deep dive into the M lang documents yet)
I'm surprised this hasn't been more of an issue previously - there seems to be a big push from microsoft to use sharepoint libraries for all file hosting requirements.