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

Question

我有一个工作簿，其中几个数据源根据某些下拉菜单/用户操作将参数传递回 SQL 查询。这可以保持工作簿精简、改进计算等 - 将所有项目级详细信息本地保存在工作簿中是不切实际的。

我的 VBA 的某些元素取决于对来自这些参数化查询的数据的评估。这里出现了问题 - 在评估宏中的所有内容之前，VBA 不会等待参数传递回查询。

我很好奇是否有人对以编程方式“暂停”VBA 执行直到提要刷新的最佳实践有任何想法或建议。我现在的工作是将我的 VBA 分为两部分，将依赖于更改数据的任何内容放入单独的函数中，并使用 application.ontime 暂停 X 秒。

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

这是 90% 的解决方案，但还不够理想。时间长度是任意的 - 在非常慢的连接上它不够长，而在快速连接上它又不必要地慢。

理想情况下，应该有某种方式等待 Excel 准备就绪然后继续。与使用 MS Internet Controls 库时类似，您可以用来

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 查询 - 它在概念上类似于命令对象，（我认为）这就是你实际上正在做的 - 并且粗略的“睡眠”循环可以用进度条或轮询标志和代码来替换其他地方的计算。

仅供参考，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

[更新]

自从我发布这个答案以来，我学到了一些东西：

如果您知道 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 效果更好 - 我发现它在数据库引擎之间有所不同。

[/更新]