如何從ExcelVBA運行查詢

下面的代碼創建一個新的 access 資料庫并將表從兩個不同的 access 資料庫復制到新的資料庫中。這部分作業沒有錯誤。

問題出在以下行：TMPConnection.Open TMPConnectionString
它通知檔案已在使用中。

Dim strPath As String
Dim objAccess As Object
Dim dbss As Object
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
strPath = Environ("USERPROFILE") & "\Desktop\TMP.accdb"
'DETERMINE IF DB ALREADY EXISTS AND CREATE IF IT DOES NOT
strDb = Dir(strPath)
Set objAccess = CreateObject("Access.Application")
If Len(strDb) <> 0 Then
    fso.DeleteFile strPath
End If
Call objAccess.NewCurrentDatabase(strPath)
Set dbss = objAccess.CurrentDb
'--------------------------------------------------------------------------------------------------
'COPY ACCESS DATABASE TABLES INTO THE NEWLY CREATED TMP DATABASE
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"
'--------------------------------------------------------------------------------------------------
'CONNECT TO DATABASE
Dim TMPConnection As ADODB.Connection
Dim TMPRecordsetN As ADODB.Recordset
Dim TMPRecordsetO As ADODB.Recordset
Dim TMPQueryN As String
Dim TMPQueryO As String
Set TMPRecordsetN = New ADODB.Recordset
Set TMPRecordsetO = New ADODB.Recordset
Set TMPConnection = New ADODB.Connection
TMPConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath
TMPConnection.Open TMPConnectionString
'--------------------------------------------------------------------------------------------------
TMPQueryN = "Select * from " & "N"
TMPQueryO = "Select * from " & "O"
Set TMPRecordsetN = TMPConnection.Execute(TMPQueryN)
Set TMPRecordsetO = TMPConnection.Execute(TMPQueryO)
'--------------------------------------------------------------------------------------------------
Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

我不確定如何使用這個“已經存在”的連接來執行查詢Set TMPRecordsetN = TMPConnection.Execute(TMPQueryN)（TMPConnection）

我的問題是：
如果已經存在與這個新資料庫的連接，我如何使用它來運行查詢（例如 TMPRecordsetN）

uj5u.com熱心網友回復：

本質上，您以兩種不同的方式連接到 MS Access：前端使用 Access COM 物件，后端使用 ADO。此外，您正在組合兩個 DB API，即 DAO withCurrentDb和 ADO with ADODB.Connection，它們都具有記錄集物件。

考慮通過 Access COM 應用程式和 DAO 使用第一個連接，或者關閉 COM 物件并使用 ADO 連接到新資料庫。

方法 1：使用 COM 連接和 DAO 運行所有操作

...
Call objAccess.NewCurrentDatabase(strPath)

objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"

' INITIALIZE DAO DATABASE
Set dbss = objAccess.CurrentDb

' OPEN DAO RECORDSETS
TMPQueryN = "SELECT * FROM [N]"
TMPQueryO = "SELECT * FROM [O]"
Set TMPRecordsetN = dbss.OpenRecordset(TMPQueryN)
Set TMPRecordsetO = dbss.OpenRecordset(TMPQueryO)

ThisWorkbook.Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

' CLOSE AND RELEASE DAO OBJECTS
TMPRecordsetN.Close: TMPRecordsetO.Close
Set TMPRecordsetN = Nothing: Set TMPRecordsetO = Nothing: Set dbss = Nothing

' CLOSE AND RELEASE COM OBJECT
objAccess.CloseCurrentDatabase
objAccess.Quit

Set objAccess = Nothing

方法2：關閉沒有DAO的COM連接并打開ADO連接

...
Call objAccess.NewCurrentDatabase(strPath)

objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr1(i), "N"
objAccess.DoCmd.TransferDatabase acImport, "Microsoft Access", fileNameNEW, acTable, arr2(i), "O"

' CLOSE AND RELEASE COM OBJECT
objAccess.CloseCurrentDatabase()
objAccess.Quit()

Set objAccess = Nothing


' CONNECT TO DATABASE VIA ADO -----------------------------------------------------
Dim TMPConnection As ADODB.Connection
Dim TMPRecordsetN As ADODB.Recordset, TMPRecordsetO As ADODB.Recordset
Dim TMPQueryN As String, TMPQueryO As String

' OPEN CONNECTION
Set TMPConnection = New ADODB.Connection
MPConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPath
TMPConnection.Open TMPConnectionString

' OPEN ADO RECORDSETS
Set TMPRecordsetN = New ADODB.Recordset
TMPQueryN = "SELECT * FROM [N]"
TMPRecordsetN.Open TMPQueryN, TMPConnection

Set TMPRecordsetO = New ADODB.Recordset
TMPQueryO = "SELECT * FROM [O]"
TMPRecordsetO.Open TMPQueryO, TMPConnection

ThisWorkbook.Worksheets("NEW").Range("A1").CopyFromRecordset TMPRecordsetN

' CLOSE AND RELEASE ADO OBJECTS
TMPRecordsetO.Close: TMPRecordsetN.Close: TMPConnection.Close
Set TMPRecordsetO = Nothing: Set TMPRecordsetN = Nothing: Set TMPConnection = Nothing

標籤：sql 擅长 vba 毫秒访问

上一篇：MicrosoftAccess表單在SQL資料庫中出現錯誤

下一篇：從訪問中的另一個表更新表