We help IT Professionals succeed at work.

Ms Access to Power BI connection via on-premises Data Gateway using ODCB

Pekka_Anttila
Pekka_Anttila asked
on
We need to connect a Access db to the Power BI via the gateway (so we can have an automatic refresh).
I believe we need to use the ODBC connection to be able to have automatic refresh.
I have not have much success implementing it.

Has anybody else successfully connected via the ODBC?

We have successfully connected a SQL database to the Power BI using the on-premises Data Gateway, refreshes automatically 8 times a day.

Our setup:
Azure cloud server (Ms server 2019)
Terminal services
SQL database
Ms Access (2019) database, split FE and BE both in Access (no plans for SQL backend for Access)
on-premises Data Gateway
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
you should be able to import Access database into PowerBI quite easily, just like what you did for SQL Server.

Untitled.jpg

Author

Commented:
Yes, I have this connection, but it does not refresh automatically.

Only gateway has a facility to automatically refresh.
Software Team Lead
Commented:
sorry as I misread your question.

for On-premises data gateway which connecting to Access database, you will need to connect via ODBC instead.

Refresh a dataset created from a Power BI Desktop file on a local drive
https://docs.microsoft.com/en-us/power-bi/refresh-desktop-file-local-drive

Author

Commented:
Connecting perfectly into the Power BI Desktop via ODBC
 
When trying to connect Power BI Service via the Enterprise Gateway, get the following error:

Activity ID:      5e7603b0-2c73-4abb-a6aa-8ba508194579
Request ID:      5341b472-146d-1f23-5e72-aea6cec9e6e7
Cluster URI:      https://wabi-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Status code:      400
Error Code:      DMTS_PublishDatasourceToClusterErrorCode
Time:      Mon Jan 13 2020 05:22:48 GMT+1030 (Australian Central Daylight Time)
Service version:      13.0.11747.315
Client version:      1912.2.031
SUDEL-APPGW:      Unable to connect to the data source undefined.
Underlying error code:      -2147467259
Underlying error message:      ODBC: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
DM_ErrorDetailNameCode_UnderlyingHResult:      -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:      Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:      dsn=ReportML_System_DSN_64
Microsoft.Data.Mashup.ValueError.Reason:      DataSource.Error

Author

Commented:
Ended up using the On-Premises Gateway in Personal mode on the terminal server, leaving RDP running all the time.

Could not get Enterprise Gateway working on the terminal server.

OCDB connection to the MS Access works good.

Scheduled update works good.

Thanks for your help.