Forum Discussion

NSRjecross's avatar
NSRjecross
Frequent Visitor
6 years ago

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

Need Help

12 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    Instead of Sharepoint.Files try Sharepoint.Contents

    Instead of API 15 try API 14

     

    see if it makes any difference.

    • NSRjecross's avatar
      NSRjecross
      Frequent Visitor
      6 years ago

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

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        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.