ADODB等待查询完成，然后刷新其他连接

本文介绍了ADODB等待查询完成，然后刷新其他连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I have a table in excel that is populated through a connection to an Access database. I am trying to write a macro that allows you to delete individual rows from this table. I use an ADODB connection to pass the delete command to Access, which works fine. However, at the end of the macro I want to refresh the table in excel so that, upon completion of the macro, the deleted record will no longer appear in that table.

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

I have already set backgroundquery = False on the connection that populates the table, but it doesn't seem to make a difference in this case. I have used the application.wait command as a workaround but I'm looking for a better solution. Is there another way to hold off the table refresh until my delete queries have run?

我的代码如下：

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

感谢您的帮助

推荐答案

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

You need to execute asynchronously and wait for it to complete.Please note that it will slow you down if you wait for each command to execute until triggering the next.

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

我们必须检查以下各项的组合

We have to check for a combination of states per the State Property documentation

进一步阅读：

ExecuteMethod
Option State Enum

这篇关于ADODB等待查询完成，然后刷新其他连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！