cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sapriorebw Frequent Visitor
Frequent Visitor

Nested ODBC and Data Gateway

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?

 

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: Nested ODBC and Data Gateway

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sapriorebw Frequent Visitor
Frequent Visitor

Re: Nested ODBC and Data Gateway

@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 

sapriorebw Frequent Visitor
Frequent Visitor

Re: Nested ODBC and Data Gateway

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

Moderator v-yuezhe-msft
Moderator

Re: Nested ODBC and Data Gateway

@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

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