vba - 如何使用 excel VBA 运行 Access 查询？

Question

我对 Access 还很陌生，我一直在尝试运行 Access 查询并使用 VBA 将结果粘贴到 Excel 中。我结合了一些我找到的代码，我想我几乎有了它，但无法弄清楚最后一步。这是代码:

Sub test()


Dim ws As Worksheet
Dim A As Object
Dim rs As Object

Application.DisplayAlerts = False

Set A = CreateObject("Access.Application")
Set ws = ThisWorkbook.Sheets("Sheet1")

A.Visible = True
A.OpenCurrentDatabase ("access database path")
A.DoCmd.OpenQuery ("query name")

Set rs = A.CurrentDb().QueryDefs("query name").OpenRecordset()

If Not rs.EOF Then
    ws.Range("A1").CopyFromRecordset rs
End If

rs.Close

 Application.DisplayAlerts = True

End Sub

我正在尝试运行查询并将结果粘贴到工作表 1 的单元格 A1 中。

我收到以下行的“运行时错误 3219”:

Set rs = A.CurrentDb().QueryDefs("query name").OpenRecordset()

任何帮助将不胜感激。

谢谢，

G

Answer 1

我修改了您的代码以从 Access 查询中获取数据，而无需创建完整的 Access.Application实例。在 Excel 2010 中测试和工作。

Const cstrPath As String = "C:\share\Access\Database2.accdb"
Const cstrQuery As String = "qryBase"
Dim dbe As Object 'DAO.DBEngine '
Dim rs As Object 'DAO.Recordset '
Dim ws As Worksheet

Application.DisplayAlerts = True 'leave alerts on during testing '
Set dbe = CreateObject("DAO.DBEngine.120")
Set rs = dbe.OpenDatabase(cstrPath).OpenRecordset(cstrQuery)

If Not rs.EOF Then
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Range("A1").CopyFromRecordset rs
End If

rs.Close
Application.DisplayAlerts = True