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

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.

Answer 3

Or you could do this.

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

Answer 4

I registered to stackoverflow just to thank MNS CHANDRASEKARAN! Since I just registered I can't mark as answer and put thi as my answer. After very very long googling without success I tried his suggestion and it worked for me. I did not include the extended properties and the system database section of his connection string and was able to connect from Access front database FE (frontend) to its BE (backend) and then create a new table via the connection object. I'm so happy for the help received, for this purpose my code follows:

...
    Dim conn As New ADODB.Connection
    Dim constr As String
    Dim sqlstr as String
    
    constr = "provider=Microsoft.ACE.OLEDB.16.0;data source=\\yournetworkpath\yourfilename.accdb;Persist Security Info=False;Jet OLEDB:Database Password=yourpassword;'"
    
    conn.Open constr, "", "", -1
    
    sqlstr = "CREATE TABLE TableName (TblField1name TEXT(20) NOT NULL, TblField2name BIT ) "
    conn.Execute sqlstr
    conn.Close
...

Since while googling I found some confusion between password protected database (let's say "the old way") and encrypted database, I want to specify that this solution worked on 2007-2016 Access encrypted database. If this is not your case you may find easier the opendatabase method that references a dao database object.