On Premise Gateway - Error connecting to Access Database DataFormat.Error

[UAV-Test-Pilot]

I have a on premise data gateway (3000.10.248 October 2019 Release 2) that won't refresh data from an Access Database hosted on a network drive. I can connect to the database and refresh perfectly fine from PowerBI Desktop, but as soon as I host it on the web, the gateway wont connect.

The Access Database was created in 32bit Access. The gateway has 64 bit powerbi installed, and I installed the 2010 64 bit Access  Database Engine. 

I am lost as far as next trouble shooting steps, and I would appreciate any help. So far I have:

- Uninstalled 64 bit 2010 Access Database Engine, and tried 32 bit 2010, but i got an error on refresh to install 64 bit

- Uninstalled 32 2010, and tried both 32/64 bit 2016 Access Database Engine, but got the same error as below

- Creating a brand new 2016 Access Database in 64 bit of just 1 table, putting it in a location where the gateway has read / write access and it still doesnt work.

To reiterate: From the same server that is running the gateway, if I launch PowerBI desktop, I can connect to and refresh the accdb Access Database located on a network share. Once published, the Gateway service displays the error when trying to refresh. I am connecting using the builtin access connector. I tried connecting using ODBC but I got error 2 listed below when trying to setup the gateway connector.

Here is the error:

Underlying error code:	-2147467259 Table: Table1.
Underlying error message:	Unspecified error
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.Detail:	Database1.accdb
Microsoft.Data.Mashup.ValueError.Reason:	DataFormat.Error
Cluster URI:	WABI-US-GOV-TB-TEXAS-redirect.high.analysis.usgovcloudapi.net
Activity ID:	6adfb028-b8ee-47a0-9c97-8cf4d0599a19
Request ID:	80ca7a70-5107-9789-9098-0c88d838e17b
Time:	2019-11-18 21:25:50Z

========== Error 2 listed below when I tried to change to ODBC connection to the Access Database =====

Activity ID:	6adfb028-b8ee-47a0-9c97-8cf4d0599a19
Request ID:	f7d53eae-59d7-b253-8106-37f8f0f8a3e0
Cluster URI:	https://wabi-us-gov-tb-texas-redirect.high.analysis.usgovcloudapi.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Mon Nov 18 2019 18:16:12 GMT-0800 (Pacific Standard Time)
Service version:	13.0.11356.194
Client version:	1911.1.024

MPCE:	Unable to connect to the data source undefined.
Underlying error code:	-2147467259
Underlying error message:	ODBC: ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:	dsn=Database1
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error

[venal]

@UAV-Test-Pilot 

Need to maintain all are the same bit of versions.

1. Power BI Desktop

2. Access Database

3. On-premises Data Gateway

Please refer the link.

If the issue is persists, can you please try to copy the network drive file to local drive and try to re-pro the issue.

If the issue is persists, please let us know.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[UAV-Test-Pilot]

@venal Appologies if I didnt explain that clearly in my original post, but I thought that would be an issue so I tried that as well.

I created a new access database in 64 bit access, and connected to it from PBI 64 bit, and have Access 64 on the gateway server.

I still get the same error.

[venal]

@UAV-Test-Pilot 

Please submit a support ticket to investigate.

[UAV-Test-Pilot · Accepted solution]

I solved the issue.

For those having this same problem, it has everything to do with the Access DB being hosted on a network drive. There are various links on Google to get user permissions corrected.

What I ended up doing is placing the Access database on a server in a folder off the main directory on the same server that is hosting the Enterprise On-Premise Gateway. I assume it should also work if the file is placed on another server as well. I then connect to the file through http NOT through network folder (This is the important part).

To Recap:

- To edit the contents inside the file, I navigate to \\server\folder\file.accdb

- To have PowerBI consume the file and get data, I navigate to http://server/folder/file.accdb

This post on another explains the issue in more technical detail for those interested...
https://forums.asp.net/t/1929989.aspx?The+Microsoft+Office+Access+database+engine+cannot+open+or+wri...