ODBC 调用失败 - 错误 3151
我们有一个 Access 数据库从 Access 97 迁移到 Acces 2007，其中一些链接表到 SQL Server 2008 数据库。 我们在 ODBC 管理器中使用文件 DSN。连接测试在那里工作得很好。事实上，我们在 Access 2007 应用程序中有一个主窗体，可以始终正确地显示数据。
当我们执行一个进行以下调用的任务时，就会出现问题：
Dim dbs As Database
调暗 rstAppend 作为记录集
设置 dbs = CurrentDb
Set rstAppend = dbs.OpenRecordset(strAccessTable, dbOpenDynaset, dbSeeChanges)
OpenRecordset 抛出错误号 3151，并且随机发生，但出现频率相当高。这是零星的。当我们没有收到错误时，任务就可以正常工作。
- 这种情况并不总是发生，大多数情况下我们都很好。
- 当发生这种情况时，尝试的所有后续任务都会发生这种情况。我们必须关闭并重新打开 Access 数据库，并向仁慈的上帝祈祷它能正常工作。
- 有时，刷新链接表管理器中的表可以解决问题，但其他情况则不能。
- 刷新链接表管理器时，我们有时会收到错误“ODBC - 调用失败”。删除文件 DSN 并创建一个新的可以解决该问题。
- 我们尝试了两种不同的驱动程序（SQL Server 和 SQL Server Native Client 10），但在这两种情况下问题仍然存在。
- 我们还尝试将 Access 文件放在与 SQL Server 相同的计算机中，但问题仍然存在。
- 我们已将所有查询的 ODBC 超时从 60 秒增加到 180 秒，但问题仍然存在。
- 我们不必等待看到错误，它会在执行任务后不到一秒内出现。
如果有人可以帮助我们找到解决此问题的方法，我们将非常高兴。
We have an Access database migrated from Access 97 to Acces 2007 with some linked tables to an SQL Server 2008 database.
We are using a File DSN in the ODBC Administrator. Connection tests work fine there. In fact, we have a main form in the Access 2007 application that shows the data always correctly.
The problem appears when we execute a task that makes the following call:
Dim dbs As Database
Dim rstAppend As Recordset
Set dbs = CurrentDb
Set rstAppend = dbs.OpenRecordset(strAccessTable, dbOpenDynaset, dbSeeChanges)
OpenRecordset throws the error number 3151 and occurs randomly but it appears quite frequently. It's sporadic. When we don't get the error, the task works fine.
- It not happens always, mostly we're fine.
- When it happens it happens for all subsequent tasks tried. We have to close and reopen the Access database and pray to the good Lord that it works.
- Sometimes, refreshing the tables in the Linked Table Manager solves the problem, but others don't.
- We receive sometimes an error "ODBC--Call Failed" when refreshing the Linked Table Manager. Deleting the File DSN and creating a new one solves the problem.
- We have tried with two different drivers (SQL Server and SQL Server Native Client 10) and in both cases the problem remains.
- Also we have tried to put the Access File in the same machine than the SQL Server and the problem remains.
- We have increased the ODBC Timeout for all the querys from 60 to 180 seconds,but the problem still remains.
- We don't have to wait to see the error, it appears in less than one second after executing the task.
We would be very happy if someone could help us to find a solution to this problem.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
经过两个月的研究，我们找到了一个解决方案，从DAO改为ADO。这是 MS 给我的答案：
我想让您意识到，实施解决方法（使用 ADO 而不是 DAO）可能比尝试找出 DAO 似乎不起作用的原因要少耗时。
因此，建议使用解决方法，而不是试图找到此问题的根本原因，特别是因为它似乎不可重现。
然而;接下来我希望您执行以下操作：
在 VBA 代码中仅使用显式类型声明而不是隐式声明。所以请替换使用DAO。在任何数据库类型声明之前，以便显式创建 DAO 对象。例如替换以下内容：
将 dbs 调暗为数据库
调暗 rstAppend 作为记录集
通过
将 dbs 调暗为 DAO.Database
Dim rstAppend As DAO.Recordset
After two months of research, we found one solution, change from DAO to ADO. This is the answer MS gave me:
I’d like to make you aware that implementing the workaround (using ADO instead of DAO) could be less time consuming than trying to figure out why DAO doesn’t seem to work.
Therefore it might be advisable using the workaround instead of trying to find the root cause of this issue, especially as it doesn’t seem to be reproducible.
However; here is what I’d like to you do next:
In VBA code use explicit type declarations only instead of implicit declarations. So please replace use DAO. In front of any database type declarations in order to explicitly create DAO objects. For example replace the following:
Dim dbs As Database
Dim rstAppend As Recordset
By
Dim dbs As DAO.Database
Dim rstAppend As DAO.Recordset
在 SQL Server ODBC 驱动程序的客户端配置中从 TCP/IP 切换到命名管道对我来说成功了。
Switching from TCP/IP to Named Pipes in the Client Configuration for the SQL Server ODBC Driver did the trick for me.
需要注意的一件事是工作站网络配置中的 DNS 设置（DNS 不是 DSN！）。
几年前，我的一位客户遇到了一些用户偶尔出现的 ODBC 断开连接问题，结果发现主 DNS 被设置为指向 Internet 提供商的 DNS。这对于 Internet 来说效果很好，但 ISP 对客户端 SQL Server 的内部 IP 地址一无所知。将主 DNS 更改为指向本地域控制器（充当本地 DNS）永久解决了该问题。
这可能不是问题的原因，但值得一看。
One thing to look at is the DNS setup (DNS not DSN!) in the network configuration for the workstations.
I had a client a couple of years ago who was encountering sporadic ODBC disconnects like this with some users, and it turned out that the primary DNS was set to point to the Internet provider's DNS. That worked fine for Internet, but the ISP knew nothing about the client's SQL Server's internal IP address. Changing the primary DNS to point to the local domain controller (which was acting as local DNS) resolved the issue permanently.
It may not be the cause of your problem, but it's worth a look.
我也遇到了这个问题。我所做的是将 DSN 添加到“用户”选项卡中的 SQL 数据库中。我注意到它在我的开发系统上运行，这是我能找到的唯一区别。在用户 PC 上创建 DSN 后，它就起作用了。
I was having this issue as well. What i did was added the DSN to the SQL database in the User tab. I noticed it was working on my dev system, and that's the only difference i could find. After creating the DSN on the users PC, it worked.