Jul 06 2022 01:30 AM
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.
Jul 06 2022 03:31 AM - edited Sep 22 2022 10:07 PM
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:
Then to import from Access, do not choose Data (tab) > Get Data > From Database > From Microsoft Access Database. Instead:
Sep 14 2022 09:39 PM
Sep 14 2022 10:14 PM
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
Sep 21 2022 11:45 PM
Sep 22 2022 10:13 PM
No idea if the following option exists in Excel 2016:
If it does, refer to my 1st reply (to pottyponder) on this discussion
Sep 25 2022 06:57 PM
I can't find the option in Excel 2016. In excel 2019 I tried this but it keeps asking for password.
Sep 26 2022 02:17 AM
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
Sep 26 2022 02:23 AM
Sep 28 2022 03:18 AM
May 31 2023 12:23 PM
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.