cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sokon
Advocate IV
Advocate IV

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 Access database file as a data source you will soon discover that the On-Premises Data Gateway doesn't provide the possibility to configure an Access Data Source. As of today, Access is still not on the list of available data source types.

 

However, an Access connection can be configured for the personal Gateway. But what if using a personal gateway is no option for you, because you might not be allowed to install it on your machine? And using a file data source for the job surely won't do the job?!

 

As it turns out, it is possible to update datasets using Access as a datasource by using the On-Premises Data Gateway!

 

What to install

  • Install the "Microsoft Access Database Engine 2016 Redistributable" (x64) in order to connect using ODBC.
  • If you want to also connect using the "File" data source type, additionally install the "Microsoft Access Runtime 2016" (x64) (You need both installations in order to get it to work, but strangely, I found I could uninstall the Redistributable after installing the Runtime and both File and ODBC connections continued to work.

Do NOT install older versions (e.g. 2010), because those seem to install the drivers in a way that the driver won't show up in the "ODBC Data Sources (64bit)" tool. I suspect the gateway being x64 is not able to find installations in the x86-folder.

 

How to connect using ODBC

Intead of the non-existant Access Connector, you can use ODBC to connect to your MDB or ACCDB-File.
Setting up an ODBC connection can be done in a couple of ways: File DSN, User DSN, System DSN and via DSN-less connection.

 

File DSN: 

Trying to put in a string starting with "FILEDSN=" in Power BI Desktop will show the message "The connection property 'filedsn' isn't supported.":

FileDSN.png

No luck here!

 

User DSN / System DSN:

For that, users need the permission to create DSNs using ODBCAD32.exe. But even if they are allowed to do so, using the same method on the gateway would require to setup User DSNs for the gateway's service account or System DSNs on the gateway server. This would be an administrative nightmare if there are more than a few Access dbs. Even worse, what if you have a cluster with more than one server? And if you have to reinstall one of them?

You surely want to avoid that!

 

DSN-less

A DSN-less connection is simply a connection string, like that: 

driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=C:\database.mdb

 

This works fine with Power BI Desktop, but using the string in a gateway ODBC data source you are likely to get errors like this:

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x1830 Thread 0x6670 DBC 0x1b7e8e68 Jet'. 
ERROR [01S00] [Microsoft][ODBC Microsoft Access Driver]Invalid connection string attribute Trusted_Connection

If you google these errors you will find a lot of possible reasons, vague solutions from Microsoft and, finally, this.
The crucial point seems to be:
Grant RWX (including modify) permissions for the user you configured for this datasource on the AppData\Local folder of the user that runs the gateway's service. We use a group "WF-OPDG-rwx" where we put the data source users and use it to grant the necessary permissions for the gateway service user's Local folder. 

 

PermissionsForTemp.pngThings to notice:

  • Always grant write permissions on the Access file for the user that is configured on the gateway, even if you want to read data. Read permissions are not enough and provoke errors as well. 
  • If you chose to install the "Microsoft Access Runtime" you can know configure a data source using the "File" option as well.

Conclusion

With the correct setup we have two working solutions: DSN-less ODBC and FILE. In out case, we use the FILE approach due to folling reasons:

  • Users usually start using the Access connector in Power BI Desktop and are annoyed when being told to change the PBIX to use ODBC. 
  • As you might know, the connection string in Power BI Desktop needs to be exactly the same like the one you configure for the gateway. When using ODBC you have to take care to choose the "Data source name (DSN)" None in order to avoid the GUI to change the connection string by adding that name. By Using the file approach you skip that possibility.
    DsnNone.png

But consider this downside:

  • By granting permissions on the Local-folder, all Access data sources use the same folder. That could be considered as a security issue.

Hopefully this post is going to prevent others from spending days looking for a solution like I did. Thanks for reading! 

13 REPLIES 13
valdo34
Frequent Visitor

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:

 

data source settings.JPG

 

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"

 

 

 

Brian_M
Responsive Resident
Responsive Resident

Just to say I had trouble could not install the Microsoft Access 2016 Runtime from the link you provided since this uses the Windows Installer technology (MSI) and there is a conflict when using the Click-to-Run version of office. 

 

Here's the link to the Click-to-run version, choose your language in step 2. and the option to download either 32-bit or 64-bit version will appear.

https://support.office.com/en-us/article/download-and-install-office-365-access-runtime-185c5a32-8ba...

 

Hope that helps someone!

 

BWL
Helper II
Helper II

Dear @Sokon and @v-yuezhe-msft

 

First of all, thank you for your post! 

 

I have a PowerBI model which combines data from an Oracle and different MS Access dbs.
Unfortunately we are stuck with the 32 bit version and we can’t change it.


My goal is to set up a scheduled refresh with the enterprise gateway and the different 32 bit MS Access dbs.

 

At the moment I have installed:

  • PowerBI Desktop 32 bit
  • Enterprise Gateway 32 bit and configured with the Oracle DB

Is it even possible to use the same approach as you did and set up a scheduled refresh via ODBC?

Thanks in advance. 

 

@v-yuezhe-msft's comment is not about the enterprise gateway but about the personal gateway. In that case, people install their own gateway and are likely to run into 32bit/64bit issues depending on various circumstances, one of them being the version of the installed Office.

 

As far as i know, there's only one version of the "On-Premises Data Gateways" that should be 64bit.

 

In your case, i think you should be fine. In my scenario we use 32bit as well.

 

It should also be possible to mix 64bit und 32bit db drivers on a gateway. You just can't run both the Access 32bit an 64bit driver on the same machine - or at least not without additional tricks.

 

Hope that helps!

 

 

@Sokon, you are right! I  have the 64 bit "On-Premises Data Gateways" installed. 

I tried your approach and I set up an file DSN for an MS Accces DB. 

But what is exactly the string I have to use, (Gateway & Desktop) when I try to connect through DSN file?

Thank you!


Using a file DSN is not my approach and it won't work. Smiley Happy Use a DSN-less connection. Example for that is in the main post.

Sorry!! It is all quite new for me! 😉
I tried your set up, and used the DSN-less connection. 

But I get the following error. 


Statuscode:400
Foutcode:DMTS_PublishDatasourceToClusterErrorCode
Tijd:Wed Jul 11 2018 16:32:20 GMT+0200 (Midden-Europese zomertijd)
Versie:13.0.5933.161



Warehouse:Kan geen verbinding maken met de mashup-gegevensbron. Raadpleeg de foutdetails voor meer informatie.
Onderliggende foutcode:-2147467259
Onderliggend foutbericht:De eigenschap 'Driver' met de waarde '{Microsoft Access Driver (*.mdb, *.accdb)}'komt niet overeen met een geïnstalleerd ODBC-stuurprogramma.
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
Microsoft.Data.Mashup.ValueError.ClientLibraryName:Microsoft Access Driver (*.mdb, *.accdb)
Microsoft.Data.Mashup.ValueError.DataSourceKind:Odbc
Microsoft.Data.Mashup.ValueError.DataSourcePath:dbq=*path*;driver={Microsoft Access Driver (*.mdb, *.accdb)}
Microsoft.Data.Mashup.ValueError.Reason:DataSource.MissingClientLibrary

 

Looks like you have the wrong driver installed on the gateway, if any. For a older driver you need to change the connection string like that: 

driver={Microsoft Access Driver (*.mdb)};dbq=C:\database.mdb

 

I just checked and I have the drivers installed, but it still doesn't work. 

 

Just to be sure, I have installed now:

  • PowerBI Desktop 32 bit
  • MS Acces 32 bit
  • Enterprise Gateway 64 bit
  • Microsoft Acces Database Engine 2010 32 bit   on the gateway server

You have exactly the same set up right?

I think so, yes!

@Sokon,

Thank you for your reply.
I have exactly the same set up, but I don't get it work.
Still can't connect because of the "wrong driver specified". 

I think the gateway 64 bit just can't connect with the 32 bit driver. 


Hope i'll find another solution someday. 🙂

@BWL: I had to move the Access connection and therefore had to redo everything I wrote on another machine. I updated the main post accordingly and you might want to try again. I added information about what I installed and fixed a wrong information in the permission part.

v-yuezhe-msft
Microsoft
Microsoft

@Sokon,

Thanks for your sharing the above methods about refreshing Access data source in Power BI Service.

I would like to give some comments on “an Access connection can be configured for the personal Gateway. But what if using a personal gateway is no option for you” in your post. The issue that using personal gateway to connect to the MS Access DB might be related to the 32 bit/64 bit Access database.

Power BI only have 64 bit personal gateway, it is by design to leverage only 64 bits drivers. Referring to the 32 bit MS Access DB, the 64bit access driver is required on the gateway machine .You can download it from here, after that the scheduled refresh could be configured successfully.

In addition, there is an idea about adding Access as data source for on-premises gateway, you can vote it up.

 



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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors