excel vba：暂停参数化SQL查询来完成？

Question

我有一个工作簿，其中几个数据提要根据某些下拉菜单/用户操作将参数传递回SQL查询。这可以保持工作簿的修剪，改进计算等 - 将所有项目级别的细节保存在工作簿中并不实际。

我的VBA的一些元素依赖于来自这些参数化查询的数据的评估。这就产生了这个问题 - 在评估宏中的所有内容之前，VBA不会等待参数传递回查询。

我很好奇，如果任何人有任何想法或建议的最佳做法，以编程方式暂停VBA执行，直到饲料刷新。我现在的工作是将我的VBA分为两部分，将取决于已更改数据的所有内容都放入单独的函数中，并使用application.ontime暂停X秒。

Application.OnTime Now + TimeSerial(0, 0, 10), "Restart" 

这是一个90％的解决方案，但它并不理想。时间长度是任意的 - 在一个非常缓慢的连接上，它不够长，在一个很快的连接上它不必要的缓慢。

理想情况下，会有某种方式等待Excel准备好然后继续。使用MS上网的时候如何类似的控件库，您可以使用

Do Until .document.ReadyState = "complete" 

暂停执行，直到IE返回就绪状态。任何更优雅的解决方案的策略？

编辑：每低于乔恩，添加代码，并解释SQL查询是如何工作的：

select sts1.studentid, sts1.alphascore as testcycle, 
sts2.numscore as lexile, sts3.alphascore as gleq, sts4.numscore as nce 

from ps.studenttestscore sts1 
join ps.students stu on (sts1.studentid = stu.id) 
join ps.studenttestscore sts2 on (sts1.studenttestid = sts2.studenttestid) 
join ps.studenttestscore sts3 on (sts1.studenttestid = sts3.studenttestid) 
join ps.studenttestscore sts4 on (sts1.studenttestid = sts4.studenttestid) 

where (stu.id = ?) and (sts1.testscoreid = 578) and (sts2.testscoreid = 575) 
and (sts3.testscoreid = 577) and (sts4.testscoreid = 576) 

的？是传递相关学生ID的参数 - MS查询使用该参数的单元格值。它看起来的细胞只是还基于选择了什么样的学生查找：

=IFERROR(INDEX(Stu!$B:$F,MATCH(Student!B2,Stu!$F:$F,0),1),999999) 

（在IFERROR只是传递一个任意数量，以防止讨厌的对话框从，如果不正确的值在某种程度上被选中弹出） 。

Answer 1

你的错误是使用MS-Query。使用ADODB编码数据库调用，并等待ADODB.Command对象的Execute方法。

...如果这就是你实际做的。这里有一定的猜测，但看起来好像查询的状态 - 不是它嵌入的表单 - 是您需要的信息。

该代码异步调用SQL查询 - 它在概念上类似于命令对象，这是（我认为）你实际上在做什么 - 原始的'sleep'循环可以被进度条或代码替换用于在其他地方投票标志和计算。可能会造成ADO对象的状态和状态属性混淆。通常，0表示关闭，1表示关闭（对于返回打开连接或数据集的对象），高于1的值对应于等待或执行。

您当然可以同步调用查询。

我可以给你'DataConnection'的代码，但你最好去ConnectionStrings.com。

 

Public Function FetchRecordSet(SQL As String, Optional CursorType As CursorTypeEnum = adOpenForwardOnly) As ADODB.Recordset 
On Error Resume Next 

Set FetchRecordSet = New ADODB.Recordset 

With FetchRecordSet 

    .CacheSize = 8 
    Set .ActiveConnection = DataConnection 
    .Open SQL, , CursorType, adLockReadOnly, adCmdText + adAsyncFetch 

    Do While .State > 1 
     Application.StatusBar = "Retrieving data... " 
     Sleep 250 
    Loop 

End With 

Application.StatusBar = False 

End Function 

[更新]

我已经学到了一些东西，因为我张贴了这个答案：

如果你知道命令的名称，rowset-返回函数或MS-Access数据库中的命名查询，请不要打扰如此调用它：

 

    SQL = "SELECT * FROM MyQuery" 
    .Open SQL, , CursorType, adLockReadOnly, adCmdText + adAsyncFetch 

打电话名字命令，并告诉数据库引擎是使用adCmdStoredProc常数命名的命令：

 

    SQL = "MyQuery" 
    .Open SQL, , CursorType, adLockReadOnly, adCmdStoredProc + adAsyncFetch 

它运行得要快得多。

查一查MSDN上的CommandTypeEnum之中，并且使用任何最适合您：

https://msdn.microsoft.com/en-us/library/ms675946(v=vs.85).aspx

使用adCmdTable命名表，看看是否可以工作比adCmdStoredProc的“视图”对象更好 - 我我发现它在数据库引擎之间有所不同。