Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NSRjecross
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
lbendlin
Super User
Super User

Instead of Sharepoint.Files try Sharepoint.Contents

Instead of API 15 try API 14

 

see if it makes any difference.

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

 

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.

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

 

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. 

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

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)

 

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

@NSRjecross  I am jumping into this thread from the older one where you and I both commented.  

FYI my current workaround is not using Sharepoint.  I have to put the AccessDB file on Network Drive and use gateway to connect dataflow to it. 

 

When I tried putting AccessDB on Sharepoint and connect using dataflow it times out.  When I tried using PBI Service dataset to refresh (after connecting AccessDB on Sharepoint from Desktop and published), failed with same error as you pointed out (OAuth2 and need gateway update).  I will update gateway and see what happens.

Our IT team updated the gateway, however now I get an error:

Unable to connect: We encountered an error while trying to connect to . Details: "Please upgrade your On-premises data gateway (or gateway cluster) to support this feature."

Even though we have the latest gateway software from MS downloads. (3000.45.7)

The error is triggered as soon as I set Oauth2 authentication and try to enter the credentials.

Full error: 

Unable to connect: We encountered an error while trying to connect to . Details: "Please upgrade your On-premises data gateway (or gateway cluster) to support this feature."Hide details

Activity ID:40da91fc-d0d8-4835-a577-6200e6620fd6
Request ID:8d118612-be86-457a-cec4-26f4db543de6
Cluster URI:https://wabi-australia-southeast-redirect.analysis.windows.net/
Status code:400
Error Code:DMTS_PowerBIDataMovementGatewayNotSupportedError
Time:Mon Jul 13 2020 16:15:00 GMT+0800 (Australian Western Standard Time)
Service version:13.0.13860.51
Client version:2007.1.01830-train

 

 

So forcing the service to send the request via our servers isn't going to be an option.

 

Back to "why doesn't the service work when desktop does"? (We'll worry about the the option to route sharepoint requests via on-premise fails later.  Refreshing cloud sources via on-premise is a weird thing to do anyway.)

@NSRjecross Yes I am running into same error.  I guess the "upgrade gateway" error message is a generic catch-all message and not tailored for this specific scenario.  At this time my only working option is to use Network Drive instead of Sharepoint.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors