title: Are Access 2010 databases not accessible with Excel if password protected using default encryption (High Security)?
body: <p>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.</p>
  
  <p>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. <strong>Both methods work fine if I encrypt the database using the "Use legacy encryption" setting.</strong></p>
  
  <p>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.</p>
  
  <p>My research has led me to <a href="http://social.technet.microsoft.com/Forums/office/en-US/44bc8e12-3b43-4c87-86dc-624883a7034c/while-in-excel-2010-is-there-a-way-to-connect-to-an-access-2010-database-encrypted-with-the-client?forum=officeitproprevious" rel="nofollow noreferrer">this Technet thread</a> and <a href="https://stackoverflow.com/questions/12037493/excel-adodb-vba-error-msg-not-a-valid-password">this Stackoverflow question</a>, 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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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;" &amp; _
                              "Data Source='FILEPATH'" &amp; _
                              ";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
  </code></pre>