Microsoft Excel VBA 连接到多个Microsoft访问数据库

Question

我目前正在尝试连接到 2 个单独的.mdb文件并执行INNER JOIN.

所以，我有2个.mdb(Sample1.mdb和Sample2.mdb(。两者都受密码保护。

当我尝试连接时，它显示一个错误，指出"it's already opened exclusively by another user, or you need permission..."。

发生错误是否因为我没有在此语句中插入password属性？如果是，如何将password属性插入此语句？

[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Application.ActiveWorkbook.Path & "Sample1.mdb]？

这是我的整个示例代码：

Dim Conn As ADODB.Connection
Dim resultSet As ADODB.Recordset
Set Conn = New ADODB.Connection
sqlStatement = "SELECT * FROM [Excel 8.0;HDR=YES;IMEX=2;DATABASE=" &
Application.ActiveWorkbook.Path & "Sample1.mdb].[SampleData$] a INNER JOIN 
[Excel 8.0;HDR=YES;IMEX=2;DATABASE=" & Application.ActiveWorkbook.Path &
"Sample2.mdb].[SampleInfo$] b ON a.Index = b.Index WHERE a.idCode = 2"
With Conn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = Application.ActiveWorkbook.Path + "Sample2.mdb"
.Properties("Jet OLEDB:Database Password") = "password"
.Open
Set resultSet = .Execute(sqlStatement)
End With

Answer 1

这有一些奇怪的事情。

1. [Excel 8.0;HDR=YES;IMEX=2;是用于连接到 Excel 文件(而不是 MDB 数据库(的连接字符串。由于您将其作为参数传递给MDB数据库，因此肯定会出现奇怪的错误。由于连接到 Access 是 Access 的本机，因此只需指定路径。
2. 我会在连接字符串中指定所有连接参数，包括 uid。

Dim Conn As ADODB.Connection
Dim resultSet As ADODB.Recordset
Set Conn = New ADODB.Connection
sqlStatement = "SELECT * FROM [" &
Application.ActiveWorkbook.Path & "Sample1.mdb].[SampleData$] a INNER JOIN [SampleInfo$] b ON a.Index = b.Index WHERE a.idCode = 2"
With Conn
.Provider = ""
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source="Application.ActiveWorkbook.Path + "Sample2.mdb;Jet OLEDB:Database Password=""password"""
.Open
Set resultSet = .Execute(sqlStatement)
End With

若要使用密码查询 Access 数据库，请在 ISAM 连接字符串中指定密码：

"SELECT * FROM [MS Access;PWD=password;DATABASE=" &
Application.ActiveWorkbook.Path & "Sample1.mdb].[SampleData$] a INNER JOIN 
[MS Access;PWD=password;DATABASE=" & Application.ActiveWorkbook.Path &
"Sample2.mdb].[SampleInfo$] b ON a.Index = b.Index WHERE a.idCode = 2"

当然，您需要确保对两个 MDB 数据库使用正确的连接字符串：使用当前代码，Sample2 受保护，但 Sample1 不受保护，Sample2 受加密保护(仅提示密码(，而不是用户级安全性(提示密码和用户名(。