Scheduled Refresh error Access database error code -2147467259

[AliPoTD]

Hello everyone,

I had several reports set up with a Schedule Refresh in Power BI Service, getting data from Access databases on the company server. All have been working successfully right up until yesterday (Sunday 9th June 2024) when all reports with Scheduled Refreshes of Access databases are failing with the error code -2147467259.

e.g. 

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Unspecified error"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Detail","detail":{"type":1,"value":"Milk Monitor v135.accdb"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataFormat.Error"}}],"exceptionCulprit":1}}} Table: Farm Details.
Cluster URI: WABI-UK-SOUTH-C-PRIMARY-redirect.analysis.windows.net
Activity ID: f526ca39-0855-4427-892d-c0637ffe94a5
Request ID: 8fc21853-c015-4b4a-9491-0fc5a4ac5a7d
Time: 2024-06-10 11:03:08Z

Nothing has happened to the reports. Saturday night all refreshing successfully, Sunday morning all reports accessing an Access database failed. 

Refreshing in Desktop works btw, the only issue is in Power BI Service.

Company IT have informed me that the servers were restarted Sunday morning between 02:00 and 05:00 but that nothing else occurred. I have a suspicion that maybe a driver or similar has been updated which may be causing the problem, but they say not. Very coincidental that everything was working fine until the Sunday morning maintenance...!

Can anyone shed any light as to what may be causing the problem please?

Many thanks 🙂

[sjm334]

How do you find that version service history?  I can't seem to find that.

[lbendlin]

That's something we maintain ourselves by screen scraping app.powerbi.com every four hours.

[Dinsha]

Hi,

I don't know if this helps you or not, but please update the gateway to the latest version. I have faced a similar issue.

Data source error: {"error":{"code":"DM_GWPipeline_Client_GatewayUnreachable","FBI.error":{"code":"DM_GWPipeline_Client_GatewayUnreachable","parameters":{},"details":[],"exceptionable":1}}} Table: my_table_name (which changes each and every time)

I resolved the issue using this.

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-tshoot 

https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem-tshoot 

[lbendlin · Accepted solution]

There was a Power BI Service version change.

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

[Ekki]

I met the same promblem, and bothered me for almost one month.

Has anyone solved this kind of errors? I want to ask for some suggestion, thanks!

[sjm334]

I encountered a similar error a few years back and was able to resolve it with an ODBC connection on the Gateway instead of a file connection.  This article was helpful: Using MS Access as a data source with On-Premises Data Gateway

A few things learned:

1. DateGateway user has to have security to the access datatabase location.  May be a service account.

2. For the ODBC connection we are using the Micosoft Access Driver. 

3. The access file format matters.  When using compact and repair it can revert depending on what data types are in the database.

4. Under File>Options>Current File>Data Load in PowerBI we select One (disable parallel loading)

There is aso some rwx access that needs granted on the PowerBI server, this is what I have in my notes:

1. Note that this says rwx access needs to be granted to C$\Users\PBIEgwService\AppData.
2. Added these permissions to \Windows\ServiceProfiles\PBIEgwService\AppData\
   1.       Noted online that this path may be OS version dependent; this is not documented well online.