超长查询经常超时。帮助？
这个特定的报告过程的工作原理如下：
- VBA 脚本告诉 Excel 文件更新其外部数据。
- Excel 文件从临时 Access 2003 数据库（存储在网络上）中提取数据，进行一些格式化以使其美观，然后将文件保存到网络驱动器。
- VBA 脚本通过电子邮件将其发送给需要数据的收件人。
我这样做已经很多年了，但偶尔会出现机器在提取数据时出现问题的问题。以下是我收到的 2 条错误消息：
- 远程服务器计算机不存在或不可用
- 自动化错误
调用的对象已与其客户端断开连接。
随着时间的推移，adhoc Access 数据库中的查询变得越来越复杂。今天，一个特定查询中又添加了 8 个 OR 条件子句，而该查询一开始已有大约 10 个。结果是，当我今天尝试运行更新后的报告时，它在成功运行之前抛出了 4 次错误。
如果可以选择，我会直接编辑查询，看看是否可以通过这种方式提高性能，但出于政治/安全原因，这不太可能。因此，我向 StackOverflow 的优秀贡献者们询问一些建议，以使其更加可靠。
Here's how this particular reporting process is supposed to work:
- A VBA script tells an Excel file to update it's external data.
- The Excel file pulls it's data from an adhoc Access 2003 db (stored on the network), does some formatting to pretty it up, and saves the file to a network drive.
- The VBA script emails it to the data-hungry recipients.
I've been doing this for years for now with the sporadic problem that the machine sometimes hiccups on the data pull. These are 2 of the error messages I can get:
- The remote server machine does not exist or is unavailable
- Automation error
The object invoked has disconnected from its clients.
As time has gone on, the queries in the adhoc Access db have grown more and more complex. Today, 8 more OR criteria clauses were added to one particular query which already had about 10 of them to begin with. The result is when I tried to run the updated report today, it threw errors 4 times before a successful run.
If I had the option, I would edit the queries directly and see if I could improve performance that way, but that is unlikely for political/security reasons. Hence, I am asking you, oh fine fellow contributors to StackOverflow, for suggestions to make this more reliable.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您无法改进查询本身，您的选择就非常有限。您收到的错误消息（以及它们是零星的事实）似乎表明问题可能是由于您在网络上运行它们而引起的。
如果可能，我会尝试将 Access 数据文件复制到运行脚本的计算机作为脚本的第一部分。
如果您无法重新链接 Access 中的表，您可以使用
subst命令 来模拟其正常网络位置（当然，假设当前链接基于映射驱动器号而不是 UNC）。Your options are pretty limited if you can't improve the queries themselves. The error messages you are getting (and the fact that they are sporadic) seem to indicate the problems may be caused by the fact that you are running them across the network.
If possible, I would try copying the Access data file down to the machine running the script as the first part of the script.
If you are unable to re-link the tables within Access, you can "fool" Access by using the
substcommand to impersonate their normal network location (assuming, of course, that the current linking is based on mapped drive letters and not UNC's).