Forum Discussion

Sokon's avatar
Sokon
Advocate V
8 years ago

Using MS Access as a data source with On-Premises Data Gateway

23.11.2018: Another update: The order of the installations is important. 22.11.2018: I updated this article. Let me know if it works for you!   When publishing a report that uses an on-Premises MS...
Tips and Tricks
valdo34's avatar
valdo34
Frequent Visitor
6 years ago

Great post - many thanks - helped me a lot with fixing my connection to MS Access - needed to swich my query from native OLEDB to ODBC due to being unable to update the data source setting on the gateway:

 

 

Here is an example M query to make that connection work in the query editor:

 

 

let
    Source = Odbc.DataSource("driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=\\path to DB\DBName.accdb", [HierarchicalNavigation=true]),
    #"\\path to DB\DBName.accdb" = Source{[Name="\\path to DB\DBName.accdb",Kind="Database"]}[Data],
    #"Query_View" = #"\\path to DB\DBName.accdb"{[Name="Query",Kind="View"]}[Data],
in
    #"Query_View"