DataFormat.Error when refreshing Access database in PBI Service

[anhngv]

I have read some thread about this error they either long ago or have not solved or too complicated. So I decide to create this post and hope there is someone who can help me out.

Let's go with a very simple case.
- I have a very simple table created in Access db (I'm using Microsoft® Access® 2019 MSO (Version 2202 Build 16.0.14931.20128) 64-bit):

- I have input the data table to PowerBI desktop (Version: 2.103.881.0 64-bit (March 2022) which only includes that simple table. And this data table can be easily refreshed in PBI desktop:

- I have successfully uploaded PBI file to service as well as setup connection/data source:

- I'm using the latest version of On-premises data gateway as well:

-> But it fails to refresh the file data source in PBI service. The error shows as below:

Underlying error code: -2147467259
Underlying error message: Unspecified error
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Detail: test.accdb
Microsoft.Data.Mashup.ValueError.Reason: DataFormat.Error
Cluster URI: WABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net
Activity ID: 592be7c6-acbe-4269-a854-2611dbf7fca1
Request ID: 479bb469-9d25-d07a-ec1f-bfa1b45325d5
Time: 2022-03-29 14:23:38Z

I'm looking to hearing your advice. Hope it's simple enough to follow.

Thanks

[anhngv · Accepted solution]

ok, atlast I solved this annoying error, and this is why and how:

- PBI desktop is OK to refresh access files, so nothing wrong with the database itself

- PBI service cannot refresh the database though all settings are OK -> there is something wrong with the connection

- While I check the connection - On premises data gateway - I found this https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-local-drive. And I see one of the data source that the gateway supports is ODBC

- Then I try Microsoft Access Database Engine at https://www.microsoft.com/en-us/download/details.aspx?id=54920

- Then atlast it works!

[GilbertQ]

Hi @anhngv 

Can you please confirm that you have got the 64bit access drivers installed?

https://support.office.com/en-us/article/download-and-install-office-365-access-runtime-185c5a32-8ba...

[anhngv]

I have installed Microsoft 365 Access Runtime x64 and restarted my computer, but there's still be the error.

Updated: After installing the program, I can't even open an Access file, then I have to remove it