如果使用默认加密(高安全性)进行密码保护，是否无法使用 Excel 访问 Access 2010 数据库? [英] Are Access 2010 databases not accessible with Excel if password protected using default encryption (High Security)?

问题描述

我目前支持由企业用户编写的 Excel 2010 电子表格和 Access 2010 数据库.Access 数据库的要求之一是对其进行加密.它是使用默认加密设置使用默认加密(更高安全性)"加密的，可以在选项 -> 客户端设置中进行设置.

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.

现在数据库受密码保护和加密，我无法通过 Excel 连接到数据库.我的测试围绕将数据导入 Excel 展开，但我真正需要做的是在日志表中创建一行.我正在尝试使用数据"选项卡和从访问"选项以及通过 VBA 代码直接导入到工作表中.使用Excel界面，出现密码对话框，永远不会接受正确的密码.使用 VBA 和 ADO，Open 语句会引发密码无效"错误.如果我使用使用旧加密"设置加密数据库，两种方法都可以正常工作.

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.

我认为这也可能是我的设置，我使用的是 Windows 7 32 位和 Office 2010.我也尝试过使用 Office 2013 的 Windows 8.1 64 位，结果相同.它适用于传统加密，但不适用于默认加密.我之前没有尝试任何东西.Office 2010 和 Windows 7 中引入了默认的更高安全性加密.

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.

我的研究使我这个 Technet 线程和 这个 Stackoverflow 问题，都表明 Excel 不能使用默认加密方法与 Access 交互.我还没有找到更多讨论这个确切问题的内容.

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.

我的问题是，使用默认设置保护 Access 2010 数据库的密码是否真的会阻止 Excel 2010 导入数据(使用密码时)?对我来说，这听起来不太对，因为在两个应用程序之间共享数据是一项非常基本的功能.我还认为，如果这是一个问题，谷歌会提供更多关于它的信息.在这一点上，我的猜测是 Excel 和 Access 默认使用下一代加密引擎，但 ADO 库尚未更新以使用它.

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.

我已附上连接代码以供审核.为了测试，我正在执行一个简单的 Now() 命令并发出结果.即使使用正确的密码，连接也会因密码无效"错误而在打开时失败.适用于传统加密，不适用于默认加密.

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

推荐答案

根据 ConnectionStrings.com，ACE 提供程序不适用于新的更强大的 Access 2010 数据库加密:

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

"注意！报告称，使用 Access 2010 - 2013 默认加密方案加密的数据库不适用于此连接字符串.在 Access 中；尝试选项并选择 2007 加密方法.这应该可以解决问题.我们不知道其他任何解决方案."

"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."

然而，这并不能说明全部.您的代码用作 Access VBA 程序并成功连接到另一个具有更强 Access 2010 加密的 ACCDB.但我找不到任何方法让类似的代码像 Excel VBA 过程一样工作.

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.

最终我放弃了这项努力.由于您的目标似乎是使包含 Access 数据的 ADO 记录集可用于 Excel，因此我决定自动化 Access 并使用其 CurrentProject.Connection.Execute 方法加载记录集.

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

注意，当我将 "SELECT Now() AS qryTest" 用于 strSelect 时，Access 在 .Quit 时崩溃了，我不明白为什么那事发生了.但该代码在 Excel 2010 中按编写的方式运行无故障.

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.

这篇关于如果使用默认加密(高安全性)进行密码保护，是否无法使用 Excel 访问 Access 2010 数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！