以下2個查詢取自不同資料庫上的表
MyQuery = "Select * from " & "T1"
MyQuery2 = "Select * from " & "T2"
我想將這些嵌套在以下查詢中
Dim rrst As New ADODB.Recordset
mkQry = "SELECT x.*" _
& "FROM (" & MyQuery & ") x LEFT JOIN (" & MyQuery2 & ") y ON " _
& "(x.F1 = y.F2) AND " _
& "(x.F1 = y.F2) AND " _
& "(x.F1 = y.F2) AND " _
& "(x.F1 = y.F2) AND " _
& "(x.F1 = y.F2)" _
& "WHERE (((y.F2) Is Null))"
rrst.Open mkQry
Worksheets("TST").Range("A1").CopyFromRecordset rrst
但是，我收到一個錯誤：
The connection cannot be used to perform this operation
在以下行：rrst.Open mkQry
我想這與MyQueryand有關MyQuery2，兩者都來自不同的資料庫。有沒有辦法使這項作業？
uj5u.com熱心網友回復：
學到了一些新東西 - Excel 可以通過一個 SQL 陳述句從多個 Access 檔案中提取資料。你在嵌套的正確道路上。必須設定一個連接，可以是作業簿或 Access 檔案之一，然后其他資料源必須嵌套嵌入檔案路徑。例子：
- 使用具有早期系結的 ADODB 物件連接到作業簿，因此需要參考 Microsoft ActiveX 資料物件 xx 庫。
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"
rs.Open "SELECT H.*, P.* FROM (SELECT * FROM Holidays IN 'C:\Users\Owner\June\Umpires.accdb') AS H " & _
"INNER JOIN (SELECT * FROM Projects IN 'C:\Users\Owner\June\LabData.accdb') AS P " & _
"ON H.HolID = P.ProjRecID", cn, adOpenStatic, adLockReadOnly
- 連接到一個 Access 檔案
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\Owner\June\LL\Umpires.accdb'"
rs.Open "SELECT Holidays.*, Pjt.* FROM Holidays INNER JOIN (SELECT * FROM Projects IN 'C:\Users\Owner\June\DOT\Lab\Data\LabData.accdb') AS Pjt ON Holidays.HolID = " & _
"Pjt.ProjRecID ", cn, adOpenStatic, adLockReadOnly
- 具有早期系結的 DAO，因此請參考 Microsoft DAO 3.6 物件庫
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\Users\Owner\June\LL\Umpires.accdb")
Set rs = db.OpenRecordset("SELECT Holidays.*, Pjt.* FROM Holidays " & _
"INNER JOIN (SELECT * FROM Projects IN 'C:\Users\Owner\June\DOT\Lab\Data\LabData.accdb') AS Pjt " & _
"ON Holidays.HolID = Pjt.ProjRecID ")
我對 PowerQuery 插件進行了快速測驗，它能夠從兩個 Access 檔案中提取資料并將資料集保存到作業表。這確實允許將兩個資料源的“實時”鏈接作為合并資料集。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/429064.html
下一篇：如何在ExcelVBA中嵌套查詢