ADODB等待查询完成，然后刷新其他连接 [英] ADODB wait for query to complete before refreshing a different connection

问题描述

我在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

您可以随时使用State属性确定给定对象的当前状态
。对象的State属性可以具有
值的组合。例如，如果一条语句正在执行，则此
属性将具有adStateOpen和
adStateExecuting的组合值。

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.

进一步阅读：

• 执行
  方法


• 选项状态枚举

• Execute Method
• Option State Enum

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