Forum Discussion
Unable to Import data from password protected Microsoft Access Database
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.
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.
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
- I tried this but I still got error. First time It look like it is working but after that I got errors.
No idea if the following option exists in Excel 2016:
If it does, refer to my 1st reply (to pottyponder) on this discussion
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)
- I have the same problem. Is this means that I cannot import the data from password protected access into excel? I have access 2016.
