excel - ADODB 在刷新不同的连接之前等待查询完成

Question

我在 Excel 中有一个表，该表通过与 Access 数据库的连接进行填充。我正在尝试编写一个宏，允许您从此表中删除各个行。我使用 ADODB 连接将删除命令传递给 Access，效果很好。但是，在宏结束时，我想刷新 Excel 中的表格，以便在宏完成后，删除的记录将不再出现在该表格中。

我已经在填充表的连接上设置了backgroundquery = False，但在这种情况下似乎没有什么区别。我已使用 application.wait 命令作为解决方法，但我正在寻找更好的解决方案。是否有另一种方法可以推迟表刷新，直到我的删除查询运行为止？

我的代码如下:

Sub Delete_recipe()

'Set up query
Dim Recipe_ID As Integer
    Worksheets("Recipe Adder").Calculate
    Recipe_ID = Range("New_recipe_ID").Value

'Open data connection
Dim Cn As ADODB.Connection
Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Tucker\Desktop\Recipe project\MURPH.xls;Extended Properties=Excel 8.0;" _
& "Persist Security Info=False"

'Execute delete query
Cn.Execute "Delete from Recipe_master IN 'C:\Users\Tucker\Desktop\Recipe project\MURPH.accdb' where Recipe_ID =" & Recipe_ID
Cn.Execute "Delete from Recipe_ingredients IN 'C:\Users\Tucker\Desktop\Recipe project\MURPH.accdb' where Recipe_ID =" & Recipe_ID
Cn.Execute "Delete from Recipe_instructions IN 'C:\Users\Tucker\Desktop\Recipe project\MURPH.accdb' where Recipe_ID =" & Recipe_ID

'Close connection
Cn.Close
Set Cn = Nothing

'Application.Wait (Now + TimeValue("0:00:06"))
ActiveWorkbook.Connections("Murph Ingredient").Refresh

'Clear data from cells
Range("New_recipe_name_merged").ClearContents
Range("Recipe_description").ClearContents
Range("New_recipe_ingredients").ClearContents
Range("New_recipe_instructions").ClearContents

End Sub

感谢您的帮助

Answer 1

您需要异步执行并等待它完成。请注意，如果您等待每个命令执行直到触发下一个命令，它会减慢您的速度。

cn.Execute sqlString, , adAsyncExecute
Do While cn.state = adStateOpen + adStateExecuting
    ' wait for command to finish
    ' perhaps show a status here
Loop

ActiveWorkbook.Connections("Murph Ingredient").Refresh

我们必须根据 State Property documentation 检查状态组合

You can use the State property to determine the current state of a given object at any time. The object's State property can have a combination of values. For example, if a statement is executing, this property will have a combined value of adStateOpen and adStateExecuting.

进一步阅读:

• ExecuteMethod
• Option State Enum