Unable to Import data from password protected Microsoft Access Database

[pottyponder]

Hi, I am trying to import data into excel from my Access database that is password protected.  It worked fine with Excel 2016 and asked me to enter a password when it connects but now I have upgraded to 2021 it fails and I get the message below,

We encountered an error while trying to connect 

details; "not a valid password."

If I go to edit and advanced there is no where to input the password.

Many Thanks Paul.

[#2]

Hi @pottyponder / Paul

Am a bit suprised this worked with 2016. Anyway...

Power Query/Get & Transform standard connectors (Excel file, Access DB...) don't support password protected files, hence the error you get

Within Excel:

• File > More... > Options
• Data > Show legacy data import wizards > Check option From Access (Legacy) > OK

Then to import from Access, do not  choose Data (tab) > Get Data > From Database > From Microsoft Access Database. Instead:

• Data (tab) > Get Data > Legacy Wizards >  From Access (Legacy)

[#3]

I have the same problem. Is this means that I cannot import the data from password protected access into excel? I have access 2016.

[#4]

@pottyponder 

If you're using the same workbook from the old version of excel, the first thing I'd try is GetData/Data Source Settings

Then select the source with the access DB and click Clear Permissions, then Click on Edit.  You should have the option to select different credential types like default, database, windows, etc.  Pick the appropriate one (I'm not sure if Access will be an option, so perhaps either database or default might work).  Then enter your credentials again.  

I'm shooting blindly here as I've no access database to test, although this method has proven successful in resolving several other credential related issues.  

Good luck.

Dexter

[#5]

I tried this but I still got error. First time It look like it is working but after that I got errors.

[#6]

@arthurloibin 

No idea if the following option exists in Excel 2016:

If it does, refer to my 1st reply (to pottyponder) on this discussion

[#7]

@L z. 

I can't find the option in Excel 2016. In excel 2019 I tried this but it keeps asking for password.

[#8]

@arthurloibin 

I don't have Excel 2016 nor 2019 so can't check how this works with those versions. However...

If Excel 2016 doesn't expose a "From Access (Legacy)" option I guess it's because that version uses this old/legacy connector by default (just a guess, not sure)

Re. "I tried this but it keeps asking for password". If you talk about the following dialog box:

It's expected as the password (for such Legacy connector) is not stored in the Data Source Settings

[#9]

Thank you. Do you by any chance have an idea on how to store the password for the legacy connector?

[#10]

As Far As I Know this isn't possible

[#11]

@pottyponder 

You can try out using OLEDB connection. 

Get Data -> From Other Sources -> From OLEDB

Click [Build] button right under the connection string input box. Therefore you will see a [Data Link Properties] dialog box. Select the latest MS Access Provider on [Provider] tab and Input the database path on [Connection] tab. Just put Admin as the user name and check the [Blank Password]. After that  click on [All] tab and search for [Jet OLEDB database password] and edit it as you setting. Please take caution that it will expose the database password within the connection string. Random user could obtain the database password by exporting the connection through Power Query or just editing the connection.