Likely the every two click error is due to opening the table that was just deleted where every other time it exists. Consider iterating through MS Access's TableDefs collection to conditionally delete the object if it exists. Then, re-order your action query to run before OpenRecordset call.
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
Aside - Avoid ever using On Error Resume Next in VBA. Always proactively anticipate and handle exceptions.
Alternatively, instead of using the make-table command SELECT * INTO and then having to worry about deleting the table programmatically, simple create your table once and then use DELETE and INSERT which can be run each time. Of course this assumes the table's structure (fields/types) remain the same.
DELETE FROM myTable;
INSERT INTO myTable (Col1, Col2, Col3)
SELECT Col1, Col2, Col3 FROM myOtherTable;
SELECT * FROM myTable;
Finally, there is no reason to even use the MS Access object library to open/close the .GUI just to run queries. Since Access is a database, connect to it like any other backend (i.e., SQLite, Postgres, Oracle) and run your queries from there. Below is an ODBC connection example which the driver can easily be swapped for drivers of other RBDMS's.
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:\Path\To\Access\DB.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
In fact, the above approach does not even require MS Access GUI .exe installed! Also, be sure to save the SELECT query (even one inside INSERT) inside Access and not run as a VBA SQL string since the Access engine will save the best execution plan for stored queries.
