在Excel中无法连接到VBA查询的Access数据库?

I have a VBA query built in excel that runs a SQL query against an Access Database. The connection works when the database is unprotected without a password using the following code:

我在excel中构建了一个VBA查询，它运行一个针对Access数据库的SQL查询。当数据库不受保护时，没有使用以下代码的密码，连接就会起作用:

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

But when I password protect the database and try the same above but with added password condition, it won't connect and I get a "Run-time error -2147217843" message. The code I'm using for this is as follows:

但是，当我保护数据库并尝试使用上面的密码时，但是添加了密码条件，它不会连接，我得到了一条“运行时错误-2147217843”消息。我使用的代码如下:

Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Cnct = Cnct & "user ID=" & Environ("Username") & ";"
Cnct = Cnct & "password=XXXXXXXXXX;"
Connection.Open ConnectionString:=Cnct

Does anybody know what I am doing wrong here?

有人知道我做错了什么吗?

1 个解决方案

#1

This is the connection string to use when you have an Access 2007 - 2013 database protected with a password using the "Set Database Password" function in Access.

这是当您有一个Access 2007 - 2013数据库时要使用的连接字符串，该数据库使用“Set database password”函数在访问中使用密码保护。

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:Database Password=MyDbPassword;

Some reports of problems with password longer than 14 characters. Also that some characters might cause trouble. If you are having problems, try change password to a short one with normal characters.

一些报告说密码问题超过14个字符。而且有些角色可能会引起麻烦。如果您遇到问题，请尝试将密码更改为一个普通字符的短密码。

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. Please get in touch if other solutions is available!

注意!有报道称，使用Access 2010 - 2013默认加密方案加密的数据库不使用此连接字符串。在访问;试试选项，选择2007加密方法。这应该能让它工作。我们不知道还有其他的解决办法。如果有其他解决方案，请联系我们!

(Source)

(源)