How to connect Excel with locked Access database using VBA?

Question

I have an Access database with password ("1234") and I want to connect an Excel file, using VBA, to extract data.

If I unprotect the Access file I can connect with something like this

path = ThisWorkbook.Path & "\KCBD.accdb"
cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Persist Security Info=False;"

Set Cn = New ADODB.Connection
Cn.Open cs

When I protect Access file with a password I don't know how to do the connection.

I tried

Ruta = ThisWorkbook.Path & "\KCBD.accdb"
cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Ruta & ";Persist Security Info=False;" & "Password=1234;"

Set Cn = New ADODB.Connection
Cn.Open cs

In the Cn.Open cs line this error makes me stop:

Runtime Error '-2147217843 (80040e4d)'

Answer 1

I found the answer in this question: Excel ADODB VBA error msg 'Not a Valid Password'

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

Thanks to bibadia user

Answer 2

For secured or password protected Access database use property "Database Password" For Example:

cs = "provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & App.Path & "\myAccessFile.accdb;" & _
    "Jet OLEDB:Database Password= 1234"

here "App.Path" is for where your Application is placed you can place Access database however if there is a specified location then replace App.Path with it in your format
e.g:

    Ruta = ThisWorkbook.Path & "\KCBD.accdb"
cs = "provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & Ruta & ";" & _
        "Jet OLEDB:Database Password= 1234"

if this wil not work then please try another Jet provider

cs = "provider=Microsoft.jet.oledb.4.0;" & _