Are Access 2010 databases not accessible with Excel if password protected using default encryption (High Security)?

Question

I am currently supporting an Excel 2010 spreadsheet and Access 2010 database that were written by business users. One of the requirements of the Access database is that it be encrypted. It was encrypted with the default encryption settings "Use default encryption(Higher security)" which can be set in Options -> Client Settings.

Now that the database is password protected and encrypted, I am unable to connect to the database through Excel. My testing revolves around importing data into Excel, but what I really need to do is create a row in a log table. I am trying both to import directly to the sheet using the "Data" tab and "From Access" selection and through VBA code. Using the Excel interface, the password dialog box comes up and will never accept the correct password. Using VBA and ADO, the Open statement throws a "not a valid password" error. Both methods work fine if I encrypt the database using the "Use legacy encryption" setting.

I thought it also may be my setup, I'm using Windows 7 32-bit and Office 2010. I have also tried with Windows 8.1 64-bit using Office 2013 with the same results. It works with legacy encryption, but not with default encryption. I didn't try anything earlier. The default higher security encryption was introduced with Office 2010 and Windows 7.

My research has led me to this Technet thread and this Stackoverflow question, both suggesting that Excel cannot interact with Access using the default encryption method. I haven't found a whole lot more discussing this exact issue.

My question to you is does password protecting an Access 2010 database using the default settings really prevent Excel 2010 from importing data (when using the password)? Something about that doesn't sound right to me since sharing data between the two applications is a pretty basic function. I also think that if it were an issue, Google would have turned up more information about it. My guess at this point is that Excel and Access are using the Next Generation encryption engine by default, but that the ADO library has not been updated to use it.

I've attached the connection code for review. For testing I am doing a simple Now() command and emitting the results. The connection fails on the open with a "not a valid password" error even when using the correct password. Works with legacy encryption, not with default encryption.

Sub ADOCNGConnect()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ds As String

    'setting up connection
    Set cn = New ADODB.Connection
    With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                            "Data Source='FILEPATH'" & _
                            ";Jet OLEDB:Database Password=password"
        .Open
    End With
    'setup recordset object
    Set rs = New ADODB.Recordset
    'retrieve new number
    rs.Open "SELECT Now() AS qryTest", cn, adOpenKeyset
    MsgBox rs!qryTest
    rs.MoveLast

    'close ADO object vars
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
End Sub

HansUp · Accepted Answer · 2014-02-04 18:44:40Z

4

According to ConnectionStrings.com, the ACE provider doesn't work with the new stronger Access 2010 db encryption:

"Note! Reports say that a database encrypted using Access 2010 - 2013 default encryption scheme does not work with this connection string. In Access; try options and choose 2007 encryption method instead. That should make it work. We do not know of any other solution."

However, that doesn't tell the whole story. Your code worked as an Access VBA procedure and successfully connected to another ACCDB which had the stronger Access 2010 encryption. But I could not find any way to make similar code work as an Excel VBA procedure.

Eventually I abandoned that effort. Since your goal seems to be to make an ADO recordset containing Access data available to Excel, I decided to automate Access and use its CurrentProject.Connection.Execute method to load the recordset.

This may seem kind of clunky, but it works ...

Const cstrPath As String = "C:\Users\hans\Documents\a2010_DbPass_foo.accdb"
Const cstrPwd As String = "foo"
Dim objAccess As Object ' Access.Application
Dim rs As Object ' ADODB.Recordset
Dim strSelect As String

Set objAccess = CreateObject("Access.Application")
objAccess.Visible = True
objAccess.OpenCurrentDatabase cstrPath, , cstrPwd

'strSelect = "SELECT Now() AS qryTest"
strSelect = "SELECT some_text AS qryTest FROM tblFoo"
Set rs = objAccess.CurrentProject.Connection.Execute(strSelect)
MsgBox rs!qryTest
rs.Close
Set rs = Nothing
objAccess.Quit
Set objAccess = Nothing

Note when I used "SELECT Now() AS qryTest" for strSelect, Access crashed at .Quit I don't understand why that happened. But the code worked trouble-free in Excel 2010 as written.

answered Feb 4, 2014 at 18:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

tonyriddle Over a year ago

Unfortunately in my particular case I'm using a multi user database located on a remote share and dont' think that automating locally would be a good idea. I also need to do an insert and not just a read. I do like the solution though, it is clunky but it would work if you have to have strong encryption on a local database. I am going to be recommending we re-encrypt the database using the legacy method.

Ahmad Bagadood Over a year ago

You are a life saver! I've been searching for hours and hours, trials and errors. Nothing worked until this career saving piece of code :) For me, it actually pops open the Access file in front of me to read the data. Is there a way to hide that like in the background? I pray god makes you as happy as you have made me :)

HansUp Over a year ago

@AhmadBagadood Is objAccess.Visible = False what you want instead?

Ahmad Bagadood Over a year ago

@HansUp Hello again :) The file is working fine with me. Shared it with my colleague and he keeps getting a "RefreshData = UpdateData error: 91 Object variable or block variable not set". The line causing the error is Set rs = objAccess.CurrentProject.Connection.Execute(strSelect) Appreciate any insights you can share!

HansUp Over a year ago

I don't know where RefreshData = UpdateData comes from. I think you or your friend would have to submit a question showing code and context in order for someone to figure this out.

|

Collectives™ on Stack Overflow

Are Access 2010 databases not accessible with Excel if password protected using default encryption (High Security)?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related