强制 vba 在继续之前等待 sql 查询执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11453592/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Force vba to wait for sql query to execute before continuing
提问by Skrealin
This seems to be a fairly common problem but none of the the solutions I've found seem to work.
这似乎是一个相当普遍的问题，但我发现的所有解决方案似乎都不起作用。
I'm grabbing some data from SQL Server and copying it into a worksheet. Then I want to copy a range from the new data and do other stuff with it. All of this happens in a single vba function.
我正在从 SQL Server 获取一些数据并将其复制到工作表中。然后我想从新数据中复制一个范围并用它做其他事情。所有这些都发生在单个 vba 函数中。
My problem is when the function is run from Excel it moves onto the second part of the function without waiting for the query to return the required data.
我的问题是当函数从 Excel 运行时，它会移动到函数的第二部分，而无需等待查询返回所需的数据。
Of course the function works fine when I run it from the vba IDE.
当然，当我从 vba IDE 运行它时，该函数工作正常。
Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset
a.Open (connStr)
Set r = a.Execute(sqlstr)
sht.Range("A2").CopyFromRecordset r
'please wait here until the proc has executed?
checkData = sht.Range("A2").Value
When I run the function from Excel checkData is always empty, when I run it with F5 it always has the required data.
当我从 Excel 运行函数时， checkData 总是空的，当我用 F5 运行它时，它总是有所需的数据。
采纳答案by mkingston
Does this work?
这行得通吗？
Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset
a.Open (connStr)
Set r = a.Execute(sqlstr)
Do
'Wait
Loop Until Not r Is Nothing
sht.Range("A2").CopyFromRecordset r
checkData = sht.Range("A2").Value
Alternatively, if this fails, you could try testing some property of r, like EOF or BOF, and if an error occurs, or you get an unexpected value you know the data hasn't yet loaded. For example:
或者，如果失败，您可以尝试测试 r 的某些属性，如 EOF 或 BOF，如果发生错误，或者您得到一个意外值，您知道数据尚未加载。例如：
Dim a As New ADODB.Connection
Dim r As New ADODB.Recordset
a.Open (connStr)
Set r = a.Execute(sqlstr)
On Error Resume Next
Do
Err.Clear
r.EOF 'Put your test here, you might test rowcount or similar.
'I've simply asked for the EOF property and ignored the result, I'm
'not sure if this will work in your case. Some testing may be required.
While Err.Num <> 0
On Error GoTo 0 'Or whatever you previously had this set to
sht.Range("A2").CopyFromRecordset r
checkData = sht.Range("A2").Value
回答by Chris
Try using:
尝试使用：
Application.CalculateUntilAsyncQueriesDone
after you execute the SQL, but before you copy the RecordSet
在执行 SQL 之后，但在复制 RecordSet 之前
Set r = a.Execute(sqlstr)
Application.CalculateUntilAsyncQueriesDone
sht.Range("A2").CopyFromRecordset r
回答by andy holaday
This might help. Instead of setting up the data source in code, set it up on the target worksheet as a data connection (Excel menu Data | From Other Sources | etc.). Once a connection object named "(Default)" is created you tap it in code along these lines:
这可能会有所帮助。不是在代码中设置数据源，而是在目标工作表上将其设置为数据连接（Excel 菜单数据 | 来自其他源 | 等）。创建名为“（默认）”的连接对象后，您可以在代码中按以下几行点击它：
With ActiveWorkbook
.Connections("(Default)").OLEDBConnection.BackgroundQuery = False
.Connections("(Default)").OLEDBConnection.CommandText = sqlstr
.RefreshAll
' do more stuff
' will wait for .RefreshAll to complete because .BackgroundQuery = false
End With
回答by DJ.
I think you need a r.movelastafter the execute to make sure all the rows are returned.
我认为您需要r.movelast在执行之后确保所有行都返回。
Something like
就像是
Set r = a.Execute(sqlstr)
If Not r.EOF Then
r.MoveLast
End If
sht.Range("A2").CopyFromRecordset r
回答by Jeff Larsen
Along the lines of andy holaday, I got this to work by unchecking "Enable background refresh" in the external data range properties. Disabling this feature forces excel to wait while the query is run.
沿着 andy holaday 的路线，我通过取消选中外部数据范围属性中的“启用背景刷新”来使其工作。禁用此功能会强制 excel 在查询运行时等待。
回答by SQLAlan
But if there is no recordset being returned I found this code will wait until the SQL code returns before going to the next VBA statement. Handy when there is dependency by one command on another or if you need an entire data set created before moving on.
但是，如果没有返回记录集，我发现此代码将等到 SQL 代码返回后再转到下一个 VBA 语句。当一个命令依赖于另一个命令或者在继续之前需要创建整个数据集时，这很方便。
Dim Con As ADODB.Connection
Dim CmdTxt As String
Set Con = New Connection
Con.ConnectionString = ThisWorkbook.GetYourConnectString()
Con.Open
CmdTxt = "EXEC db.schema.StoredProc1 @Param1 = 'Yes'"
ExecuteSql Con, CmdTxt, True, True
CmdTxt = "EXEC db.schema.StoredProc2 @Param1 = 'No'"
ExecuteSql Con, CmdTxt, True, True
MsgBox "Both commands completed sequentially"
The code for ExecuteSql is:
ExecuteSql 的代码是：
Public Function ExecuteSql(Con As ADODB.Connection, sql As String, _
Optional StopOnError As Boolean = True, _
Optional WaitUntilDone As Boolean = False) As String
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.CommandType = 1
.CommandText = sql
.ActiveConnection = Con
If WaitUntilDone = True Then
.CommandTimeout = 0 'set timeout to unlimited
.Execute , , adExecuteNoRecords 'no records value speeds up internal code
Else
.Execute
End If
End With
ExecuteSql = ""
Exit Function
