Nested ODBC and Data Gateway

[sapriorebw]

I am using a nested ODBC [ PBI --ODBC--> Access --ODBC--> Company DB ] to pull data into PBI. When I try to set up a scheduled refresh I get the following error:

Underlying error code:	-2147467259 Table: < PBI Table>.
Underlying error message:	ODBC: ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to '< Company DB >' failed.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:	dbq=< File Path >;driver={Microsoft Access Driver (*.mdb, *.accdb)}
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error
Cluster URI:	WABI-WEST-US-redirect.analysis.windows.net
Activity ID:	ad393298-bcd2-4bd5-99d9-ccf5965c49e7
Request ID:	f7a666ce-b29c-4f27-9f50-6f90a4f6b543
Time:	2018-07-20 19:38:29Z

 I imagine this is from the nested ODBC call. Is this correct? Can I make this work?

[v-yuezhe-msft]

@sapriorebw,

Do you use ODBC database option to connect to Company DB in Access? If so, why not directly create DSN for the company DB and connect to this data source in Power BI Desktop?

Also, why not directly use Access database connector in Power BI Desktop to connect to the Access data source?

Regards,
Lydia

[sapriorebw]

@v-yuezhe-msft

Yes, I am using ODBC (through DNS) to connect to the Company DBs in Access. Under normal circumstances I would, however, company policy has not approved Power BI as an acceptable program to connect directly to our DBs. With regards to your second query, Access does not have native support with the Enterprise Gateway.

Thanks 

[v-yuezhe-msft]

@sapriorebw，

For the second option, you can use personal mode gateway instead, and ensure that you install 64 bit Access driver in the gateway machine.

Regards,
Lydia

[sapriorebw]

Additionally, both company DBs run on 32 bit architecture. This may also cause the failures I'm experiencing.