In my previous article, I examined connecting Power BI Desktop to an unencrypted Access database.  You can read all the details at:

Connecting Power BI Desktop To An Unencrypted Access Database

I’ve be dabbling with Power BI Desktop to build a dashboard for a client and thought I’d share just how easy it is to connect an unencrypted Access database to Power BI Desktop to be able to produce some truly nice charts. I emphasize Unencrypted, so no password on the database, as Microsoft has made…

Continue reading →

What About Encrypted (Password Protected) Databases?

Now, since every database should be encrypted as data should never be unsecured, I then turned my attention to connecting Power BI Desktop to a password protected Access database (encrypted database).

Access Connector

So, I followed the initial steps from my previous article; I went and selected the Access connector, selected my file and was quickly presented with the following error message:

Okay, I thought to myself, Microsoft probably gave us the ability to add the credentials if I select the Edit option on this error dialog, so I clicked it and got presented with:

So, I quickly found out, that Microsoft’s Access connector does not provide any means to input database credentials, thus can not be used to access the data within an encrypted Access database!

So Microsoft is luring users down the wrong path right from the start.  What a waste of time and frustrations for no reason.

How hard would it have been to add a password control!  Once again, I had no words!  Another half-baked, half-completed tool from Microsoft that obviously had never truly been tested.  A Microsoft Access database connector that can’t handle password protected (encrypted) databases, how stupid is that in 2022!

Moving on.

The Solution

So the question quickly became:

What is one to do?
How can we connect an encrypted Access database to Power BI Desktop?

Once again, I used the Get data command, and selected More… and slowly scrolled through all the options and my eyes stopped on a familiar sight for us database people, the ODBC connector!  Could it be that simple (the answer is yes).

So let’s get started.

1. First launch Power BI Desktop
   
2. Click on Get data from the Home tab
3. From the dropdown, click on More…
   
4. From the Get Data dialog, scroll down and select ODBC and click on Connect
   
5. Next you will presented with the From ODBC dialog where you need to insert the appropriate connection string for your database file.For an Access database the syntax is:

   Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=YourDatabaseFullPath\YourDatabaseName.Extension;

   Notice how we do NOT include the credentials in the connection string!

   Click OK

6. Now, you’ll be presented with the ODBC driver dialog where you need to input the database password.Also notice, we input the User name of Admin (this is the default value and must be entered for this to work).
   Click OK
7. Give Power BI a moment to establish the connection and do what it has to do, there’s a good chance you’ll see the Connecting message, just be patient.
   
8. Finally, you will be presented with the Navigator dialog from which you can select the tables/queries you wish to select to use in Power BI to build your charts from.
   
   Once your selection made, click on Load and you are finally ready to start building your charts.   Enjoy!

A Few Resources on the Subject

Power BI Blog—Updates and News | Microsoft Power BI

Keep up with the latest Power BI updates, announcements, information, and new features on the Power BI blog. Search by category or date published.

Microsoft Power BI Community - Forums

Microsoft Power BI Forums for getting help

Power BI on Microsoft Learn

Read, create, or extend reports and dashboards? Increase your skills here.