cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

reading access databases hosted in sharepoint libraries: works in desktop, fails on service

I realised I had necroed an old thread with a previous reply when I suspect I should have made a new one as I suspect this is a bug somewhere rather than looking for workaround like the previous poster. Also does this belong better in the Service forum?

 

I'm having an issue reading access databases hosted in sharepoint libraries from reports published on the PBI service. (We are running a premium SKU for our powerBI service if that is part of the equation)

 

I can access and pull the data correctly out of the sharepoint hosted access database file from powerbi desktop.

 

I publish the file and check the settings... no on-premise gateway selected, oauth2 authentication credentials are set on the dataset. But if you try to refresh you get what looks like the old MDAC missing error:

Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: Microsoft Access: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.. The exception was raised by the IDbCommand interface. Table: ReadAccess.
Cluster URI: WABI-AUSTRALIA-SOUTHEAST-redirect.analysis.windows.net
Activity ID: 40da91fc-d0d8-4835-a577-6200e6620fd6
Request ID: df900c67-2458-e2ee-aafa-7e549274896e
Time: 2020-07-08 08:11:19Z"

 

When we had this same error for access and excel files read from our corporate file systems, we just needed to install the accessengine components to the on-premise gateway host and it resolved the issue.  Except now this file access isn't running on the gateway/server we control (service to sharepoint) so we don't have a server to add the missing access components to.

I tried to force the service to read via the gateway however I couldn't force an oauth2 connection to sharepoint in the gateway settings so always failed authentication.

 

Again, the report runs fine from PBI desktop then fails when published.

 

Any suggestions how to make the hosted report (dataset) read the database?

Thank you

Jennifer

 

M-code from a simplified test database and report (yell if you want the actual documents but they are trivial examples):

let

    Source = SharePoint.Files("https://nsrltd.sharepoint.com/sites/DataManagement", [ApiVersion = 15]),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".accdb")),

    #"Gateway_test accdb_https://nsrltd sharepoint com/sites/DataManagement/Shared Documents/" = #"Filtered Rows"{[Name="Gateway_test.accdb",#"Folder Path"="https://nsrltd.sharepoint.com/sites/DataManagement/Shared Documents/"]}[Content],

    #"Imported Access" = Access.Database(#"Gateway_test accdb_https://nsrltd sharepoint com/sites/DataManagement/Shared Documents/"),

    _LookInHere = #"Imported Access"{[Schema="",Item="LookInHere"]}[Data]

in

    _LookInHere

12 REPLIES 12
Highlighted
Community Champion
Community Champion

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

Instead of Sharepoint.Files try Sharepoint.Contents

Instead of API 15 try API 14

 

see if it makes any difference.

Highlighted
Frequent Visitor

Re: 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).

 

Highlighted
Community Champion
Community Champion

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

Ah, 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.

Highlighted
Frequent Visitor

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

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

 

Highlighted
Community Champion
Community Champion

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

Is there any chance in your reality to dump Access and move to an enterprise database like SQL server?

Highlighted
Frequent Visitor

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service


@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. 

Highlighted
Community Champion
Community Champion

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

@NSRjecross   "the gateway server"  made me chuckle. For business continuity I always employ gateway clusters with members distributed geographically (not just a bunch of VMs on the same physical host).  But this comes with its own problems, like in your case.  Not just do you need the access engine on all cluster members, it also has to be the 64 bit version.  Endless fun can be had configuring that, not entirely unlike herding cats.

Highlighted
Frequent Visitor

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

Though in this case if I could force the connection via one of our on-premise gateways, then I'd be able to ensure the libraries were installed. Wonderr which is easier to fix - oauth2 for the gateway or Access.database() for sharepoint [contents]

I haven't tried running it outside premium capacity though.. I'll give that a go monday

Any other suggestions? Does anyone have this arrangement actually working?

Jen

Herding cats is easy with a can opener. Not sure quite what to feed O365 (apparently cash and souls isn't enough)

 

Highlighted
Frequent Visitor

Re: reading access databases hosted in sharepoint libraries: works in desktop, fails on service

Update: as of this morning it appears OAuth2 authentication has been added to the on-premise gateway for Sharepoint connectors. (But you need to upgrade the gateway software before you can define a connection using it). 

This will at least let us route the query past somewhere we have installed the required modules.

 

I also found you can examine the connection string used to query the access database via the xmla interface (provider=microsoft.powerbi.olddb in case anyone was interested)

Getting closer I think

Jennifer

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors