另一个 ODBC 调用失败主题
我正在运行 Access 2010 FE 和 SQL Server 2005 BE。 我可以使用 DSNless 连接成功地对 SQL Server 执行直通查询。
在测试阶段，有时我需要恢复数据库以恢复到原始记录，以便我可以重新运行查询。我发现当我运行传递查询时，它会在我的 SQL Server 上创建一个活动连接。我通过 SQL Server 管理控制台在 MANAGEMENT | 下看到连接。 SQL Server 日志 |活动监视器，选择查看进程。当我运行传递查询时，我可以看到正在使用哪个进程 ID 以及谁在使用它。
现在，我恢复数据库的唯一方法是终止进程，例如活动连接。
现在，当我恢复的数据库就位并重新运行传递查询时，我收到一个 ODBC - 调用失败消息框。我尝试运行一个过程来刷新我的查询定义，但无济于事，当我单击这些对象时，我仍然会收到 ODBC——调用失败消息框。
现在有两种解决此问题的选项，无论哪种情况我都发现对用户不友好。
- 重新启动我的 Access 应用程序
- 等待大约 5-10 分钟重新运行传递查询
我创建了一个函数来捕获 ODBC 错误，结果如下：
ODBC 错误号：0
错误描述：[Microsoft][ODBC SQL Server 驱动程序]通信链接失败
ODBC 错误号：3146
错误描述：ODBC--调用失败。
因此，如果由于某种原因，我需要重新启动 SQL Server 或终止 SQL Server 上的进程（活动连接），而 Access 应用程序当前通过 ODBC 连接，则通过 ODBC 创建的对象将无法正常执行，直到我执行 2 个解决方法解决方案如上所述。
任何人都可以就解决方案提供一些建议吗？我很欣赏任何见解。
I am running Access 2010 FE and SQL Server 2005 BE.
I can execute pass through queries to my SQL Server succesfully by using DSNless connections.
During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.
Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection
Now when I have my restored database in place and re-run the pass through query, I receive a ODBC -- Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC-- Call Failed message box when I click on those objects.
Now there are two options on how to fix this problem, which in either case I find not USER Friendly.
- Restart my Access Application
- Wait approx 5-10 minutes to rerun the Pass Through Query
I created a function to trap my ODBC Errors and this is what appears:
ODBC Error Number: 0
Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure
ODBC Error Number: 3146
Error Description: ODBC--call failed.
So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not perform properly till I execute the 2 workaround solutions as stated above.
Can anyone shed some advice on a solution? I appreciate any insight.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
前段时间我也问过类似的问题，一直没有得到满意的答案。我原来的问题在这里： 强制 SET IDENTITY_INSERT从 MS Access 更快生效
有一个注册表设置 记录在此处用于控制超时行为的 ACE：
因此，作为第三种解决方法（除了您已经列出的两种解决方法之外），您可以将该注册表设置更改为更短的超时（例如 10 秒）。这就是我在回答中采取的方法。需要注意的是，缩短超时可能会导致性能或其他问题。您的里程可能会有所不同。
查看我的完整答案 转到原始问题以获取更多信息。
I asked a similar question some time ago, and never got a satisfactory answer. My original question is here: Force SET IDENTITY_INSERT to take effect faster from MS Access
There is a registry setting documented here for ACE that controls the timeout behavior:
So as a third workaround (in addition to the two you already listed) you can change that registry setting to a shorter timeout (like 10 seconds). This is the approach I took in my answer. One caveat is that shortening the timeout may cause performance or other issues. Your mileage may vary.
See my full answer to the original question for more info.