MS Access DB dataset refresh using enterprise gateway error

[Waldemar_Hachaj]

Hello,

I'm trying to connect to Access database file on network drive and have it refreshed automatically. In Power BI desctop it works wether using file, folder, access connector or ODBC connection. When trying to refresh dataset using online enterpise gateway I'm experiensing errors and refresh fails.

We already tried to follow article listed below:

https://community.powerbi.com/t5/Power-Query/Using-MS-Access-as-a-data-source-with-On-Premises-Data-...

What would be best method to create connection to the access accdb file on network drive to enable refresh trough enterprise gateway and what would be best configuration for the said gateway?

Erros we encountered using direct access to the file:

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":"Dentsply Sirona dashboard DB.accdb"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataFormat.Error"}}],"exceptionCulprit":1}}} Table: tbl_Daily_SBU.
Cluster URI: WABI-US-EAST2-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 65f8d379-a183-4334-89f7-17ce7d9fd30d
Request ID: 3229ec2a-cc14-b468-d7e3-48340f1c1833
Time: 2022-08-25 06:53:12Z

[Waldemar_Hachaj · Accepted solution]

After working with Microsoft support we arrived at following solutions:

1. If you want to use ODBC to access Access DB file on server other than the one on which the gate is you have to give the account that is used to authenticate gateway access Admin level privileges. This is linked to the fact,  that it needs to modify registry HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and when using a local account without admin rights it is unable to do so.

2. Use access trough FOLDER (i.e. create gate to the folder in which the DB is kept) and after navigating to the file expand Data column. In my case this was not working initially, becouse we did not have all necessary drivers installed.

[v-yetao1-msft]

Hi @Waldemar_Hachaj 

In Desktop , you can connect to MS Access DB with ODBC , and it is successful . In Service , you can also use ODBC to configure your data source in Manage gateways as the link mentioned .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Waldemar_Hachaj]

Dear Ailsa,

Unfortunately when I try ODBC method it works only on Desktop version. When trying to create Gateway connection I'm getting following error (sorry for long text):

Unable to create data source. Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid. Please verify the data source configuration and contact a data source administrator to troubleshoot this issue.



Details: GatewayXXXXXXXXXXXX: ODBC: ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2fbf0 Thread 0x2f610 DBC 0xce241458 Jet'. ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2fbf0 Thread 0x2f610 DBC 0xce241458 Jet'. ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Your network access was interrupted. To continue, close the database, and then open it again. ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2fbf0 Thread 0x2f610 DBC 0xce241458 Jet'. ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2fbf0 Thread 0x2f610 DBC 0xce241458 Jet'. ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] Your network access was interrupted. To continue, close the database, and then open it again. Please have this information handy if you choose to create a support ticket. ActivityId: 132169cc-3e06-459f-94ee-7ef0478444a2 RequestId: e4f1082f-16b7-456f-9349-4a8ffa63c3a0 Cluster URI: https://api.powerbi.com Status code: 400 Time: Thu Sep 15 2022 12:15:47 GMT+0200 (Central European Summer Time)

Best Regards,

Waldemar