3

Without a password, I can easily import the access file, but when it is password protected, it just errors. The method i'm using is shown below: Process

The error with a password protected file is shown below: Error

The suggested method to open PW protected Access files are is:

Set db = OpenDatabase("C:\myDB.accdb"), False, False, "MS Access"; PWD="somePassword")

The issue now is:

The script shows that it will open the database using the hardcoded password, but how do I tell the Data -> Get Data -> From Database -> From Microsoft Access Database, to use that password when importing?

3
  • what about opening them in Access then exporting to Excel? – Máté Juhász Mar 29 '18 at 11:37
  • 1
    to your update: that works only if you write the whole import process in a macro, and can not be mixed with manual importing – Máté Juhász Mar 29 '18 at 11:38
  • That works fine as well. As long as it is capable of auto exporting tables to excel without need of human intervention. I don't know how to program using Access VBA that's why I sticked with the other way around. With regards to the macro, after a connection has been established between excel and access, you can create a refresh macro that will refresh the imported access tables. That's what I did but then the issue is that this one is password protected – Pherdindy Mar 29 '18 at 11:39
2

If you want to import data from a protected Access file using the GUI, you can use an OLEDB connection:

Go to Data -> Other sources -> From OLEDB:

enter image description here

As your OLEDB string, enter:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Path\To\File.accdb";Jet OLEDB:Database Password="MyPassword";

Or, if you're using an MDB file:

Provider=Microsoft.JET.OLEDB.4.0;Data Source="C:\Path\To\File.mdb";Jet OLEDB:Database Password="MyPassword";

Dropdown the Advanced Options, and in the box underneath, enter your query. E.g. if you want to import Table1:

SELECT * FROM Table1

SELECT * FROM Table1

Then, press OK, view the data, and press Load. The data should now load.

8
  • After pressing OK I get another window and asks for credentials. Then it errors if I put random username and password – Pherdindy Mar 29 '18 at 12:00
  • I've revised the answer. If you've set it up correctly, there should not be a prompt for credentials. You need the quotes if either your path or your password contains spaces. If your password contains quotes, double up those quotes (or choose a different password). – Erik A Mar 29 '18 at 12:06
  • Thanks. I changed the .accdb to .mdb since that's my access file extention. Do I revert it back to .accdb? – Pherdindy Mar 29 '18 at 12:08
  • That's kind of relevant information for your question. mdb is a format from over 10 years ago, I haven't tested my code for mdb files. Of course, you should use a valid path to your file. – Erik A Mar 29 '18 at 12:09
  • This is my string it still asks for credentials: provider=Microsoft.ACE.OLEDB.12.0;data source="C:\Users\Asus\Google Drive\System Improvement\Test.MDB";Jet OLEDB:database password="god"; – Pherdindy Mar 29 '18 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.