从 Excel VBA 运行访问查询

Question

我正在尝试在 excel vba 中编写一个宏，该宏只需打开一个访问数据库并在 Access 中运行 2 个查询。它似乎可以工作，每单击一次宏按钮即可运行查询。我的意思是我单击它，它可以工作，第二次单击我在第二次单击时得到"运行时错误 462"，第三次单击它有效，第四次单击我再次收到错误，依此类推。我似乎想不通为什么会这样。这是下面的代码。

Sub QueryAccess1()
Dim db As Access.Application
Set db = New Access.Application
'set variables
db.Visible = True
db.OpenCurrentDatabase ("DatabaseFileName")
'open database
'--------------------------------------------------------------
On Error Resume Next
db.DoCmd.DeleteObject acTable, "TableName"
'if the table does not exist it skips this line
'--------------------------------------------------------------
On Error GoTo 0
'sets the error back to normal
'--------------------------------------------------------------
CurrentDb.Openrecordset ("QUERY1")
CurrentDb.Execute ("QUERY2")
'Calls the queries
'--------------------------------------------------------------
'--------------------------------------------------------------
db.CloseCurrentDatabase
db.Quit
'Closes Access
'--------------------------------------------------------------
Set db = Nothing
End Sub

当我收到错误时，我正在在线上得到它

CurrentDb.Openrecordset ("QUERY1")

Answer 1

我设法让它与Parfait的方法一起工作。这是我得到的。

Sub QueryAccess1()
Dim conn As Object, rst As Object
Dim path As String

Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
path = Sheets("SheetName").Range("A1")
'OPEN CONNECTION
conn.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & path
'DELETES TABLE CONTENTS
conn.Execute "DELETE FROM [Table1]"
'RUN UNION QUERY AND INSERT INTO TABLE
rst.Open "SELECT * FROM [Query1]", conn
conn.Execute "INSERT INTO [Table1]  select * from [QUERY1] "
Set rst = Nothing: Set conn = Nothing
End Sub

Answer 2

每单击两次错误可能是由于每隔一段时间打开刚刚删除的表。请考虑循环访问 MS Access 的 TableDefs 集合，以有条件地删除对象(如果存在)。然后，重新排序操作查询以在 OpenRecordset 调用之前运行。

Public Sub RunQueries()
On Error Goto ErrHandle:
' DAO REQUIRES REFERENCE TO Microsoft Office X.X Access Database Engine Object Library
Dim tbl As DAO.TableDef     
Dim rs As DAO.Recordset
Dim db As New Access.Application
db.Visible = False                   ' KEEP DATABASE RUNNING IN BACKGROUND
For Each tbl in db.CurrentDb.TableDefs
If tbl.Name = "TableName" Then
db.DoCmd.DeleteObject acTable, "TableName"
End If
Next tbl
' ASSUMED AN ACTION QUERY
db.CurrentDb.Execute "QUERY2", dbFailOnError
' ASSUMED A SELECT QUERY BUT CALL BELOW IS REDUNDANT AS IT IS NEVER USED
Set rs = db.CurrentDb.OpenRecordset("QUERY1")   
ExitHandle:
' FREE RESOURCES
Set rst = Nothing: Set conn = Nothing
db.CloseCurrentDatabase
db.Quit
Set db = Nothing
Exit Sub
ErrHandle:
MsgBox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
Resume ExitHandle
End Sub

另外 - 避免在 VBA 中使用On Error Resume Next。始终主动预测和处理异常。

---

或者，无需使用 make-table 命令SELECT * INTO然后不必担心以编程方式删除表，只需创建一次表，然后使用每次都可以运行的DELETE和INSERT。当然，这假设表的结构(字段/类型)保持不变。

DELETE FROM myTable;
INSERT INTO myTable (Col1, Col2, Col3) 
SELECT Col1, Col2, Col3 FROM myOtherTable;
SELECT * FROM myTable;

---

最后，甚至没有理由使用 MS Access 对象库来打开/关闭 .GUI 只是为了运行查询。由于Access是一个数据库，因此请像任何其他后端(即SQLite，Postgres，Oracle)一样连接到它，并从那里运行查询。下面是一个 ODBC 连接示例，可以轻松地将驱动程序交换为其他 RBDMS 的驱动程序。

Dim conn As Object, rst As Object
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
' OPEN CONNECTION
conn.Open "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};" _
& "DBQ=C:PathToAccessDB.accdb;"
' RUN ACTION QUERIES
conn.Execute "DELETE FROM myTable"
conn.Execute "INSERT INTO myTable (Col1, Col2, Col3)" _
& " SELECT Col1, Col2, Col3 FROM myOtherTable"
' OPEN RECORDSET
rst.Open "SELECT * FROM myQuery", conn
' OUTPUT TO WORKSHEET
Worksheets("DATA").Range("A1").CopyFromRecordset rst
rst.Close

事实上，上述方法甚至不需要安装MS Access GUI.exe！此外，请确保将SELECT查询(即使是INSERT内部的查询)保存在 Access 中，而不是作为 VBA SQL 字符串运行，因为 Access 引擎将为存储的查询保存最佳执行计划。