Automation error - when connecting to 2010 accdb password protected database from excel 2007 vba

[dawood_s]

I get Automation error when connecting to 2010 accdb password protected database from excel 2007 vba. The code is as below :'DIM STATEMENTSDim strMyPath As String, strDBName As String, strDB As String, strQuery As String, strMyDBPassword As StringDim i As Long, n As Long, lFieldCount As LongDim rng As Range'instantiate an ADO object using Dim with the New keyword:Dim adoRecSet As New ADODB.RecordsetDim connDB As New ADODB.Connection'--------------'THE CONNECTION OBJECTstrDBName = "33mSTUPV2.accdb"strMyPath = "v:\Arch_33"strDB = strMyPath & "\" & strDBNamestrMyDBPassword = "abcde"'strMyDBPassword = ""strQuery = "ds_Query1"connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";Jet OLEDB:Database Password=strMyDbPassword;"If i remove the password on the accdb file and try the above code with strMyDBPassword="", I am able to connect and proceed further.I want the code to work with the password on the accdb file.Can someone hlep. please?

 

[RoryA]

If I remember correctly there is an issue with the encryption method used from 2010 - you need to ensure the database uses a lower encryption method that is backwards compatible.

 

[dawood_s]

How do I make the encryption backward compatibel in Access 2010?

 

[RoryA]

Open it in Access, remove the password, then go into File - Options, select the Client Settings section, and at the bottom, check the "use legacy encryption" option, then add the password again.

 

[dawood_s]

Thanks for the help.However, even after setting the "use legacy encryption" , I am getting the Automation error.Could this error be related t osmething else? Strange thing is that I do not get the error when the Access database is not password protected.

 

[RoryA]

Ah, I misread your code in the original post - you have included the password variable as part of the literal connection string, so it is passing the actual text "strDBPassword" as the password. You need:

'DIM STATEMENTS
Dim strMyPath As String, strDBName As String, strDB As String, strQuery As String, strMyDBPassword As String
Dim i As Long, n As Long, lFieldCount As Long
Dim rng As Range
'instantiate an ADO object using Dim with the New keyword:
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection
'--------------
'THE CONNECTION OBJECT
strDBName = "33mSTUPV2.accdb"
strMyPath = "v:\Arch_33"
strDB = strMyPath & "\" & strDBName
strMyDBPassword = "abcde"
'strMyDBPassword = ""
strQuery = "ds_Query1"
connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDB & ";Jet OLEDB:Database Password=" & strMyDbPassword & ";"

 

[dawood_s]

RoryA,Thanks a lot. It has worked. Should I now try to restore the envryption level of Accesss 2010 to its default and check my Excel 2007 macro?

 

[RoryA]

Yes, why not?