Excel ADODB VBA error msg 'Not a Valid Password'

Question

I am trying to open a Connection to Password protected AccessDB, through Excel VBA.

I am using the following connection string

conn.Provider = "Microsoft.ACE.OLEDB.12.0"
conn.ConnectionString = "Data Source=dbPath; Jet OLEDB:Database Password=pwd;"

where dbPath is the path of accdb/mdb file and pwd is the password to the dbfile. The password is absolutely correct but I am always getting the error "Not a Valid Password". Could someone please guide me in this?? -Sri.

Additional Info: I am using office 2010 and saved this database as mdb(2003) file(I tried saving this file with accdb extn also). When i am trying to set a password to the database, it shows the msg "Encrypting with a block cipher is incompatible with row level locking. Row level locking will be ignored." Does it have anything to do with the error msg in VBA?

Answer 1

In Access 2010 the encryption method changed for database passwords, and the ADO provider's "Jet OLEDB:Database Password" keyword does not appear to work with the new method. Here, I had to remove the password, go into Access->File->Options->Client Settings->Advanced and check "use legacy encryption", then recreate the password.

I do not know if there are any new OLE DB connection string keywords for the ACE provider or whether this problem also occurs when using ADO.NET

Answer 2

Or you could do this.

    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:Database Password=" & pwd

Answer 3

Please use as below

conStr = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=C:\Users\csub1\Desktop\empdata.xlsx;Persist Security Info=False;Jet OLEDB:Database Password=pass@123;Extended Properties='Excel 12.0;HDR=Yes;';Jet OLEDB:System database=C:\Users\csub1\AppData\Roaming\Microsoft\Access\System.mdw"

Please find the System.mdw file and give it under System Database. While giving password please use Jet OLEDB:Database Password.

After lot of research it worked.