来自 VBA 的 ADODB 连接停止工作

ADODB connection from VBA stopped working

提问by asc99c

We've got an Exccel spreadsheet used to manage the rota for the out of hours support engineers. A while back, I added a bit of VBA code which automatically diverts the support phone numbers to the engineer's phone out of hours.

我们有一个 Excel 电子表格，用于管理非工作时间支持工程师的轮值表。不久前，我添加了一些 VBA 代码，它会在下班后自动将支持电话号码转移到工程师的电话上。

It does this by connecting to a database run by the phone providers, and updating the divert number.

它通过连接到电话提供商运行的数据库并更新转移号码来实现这一点。

This afternoon, it is suddenly not working on the central server:

今天下午，中央服务器突然不行了：

Dim Db As ADODB.Connection

Sub ConnectDatabase()
  Set Db = New ADODB.Connection
  Db.Open "SupportMobileDb"
End Sub

The code stops at the New ADODB.Connectionline, and reports:

代码停在该New ADODB.Connection行，并报告：

Run-time error '430':
Class does not support Automation or does not support expected
interface

I can still run the macro on my laptop, and it works correctly. And on the central server, I can still use Excel, and connect to the datasource correctly. It just won't work through VBA any more on this server.

我仍然可以在我的笔记本电脑上运行宏，并且它可以正常工作。而在中央服务器上，我仍然可以使用Excel，并正确连接到数据源。它在这台服务器上不再通过 VBA 工作了。

The macro ran correctly at 9am and diverted all the phones to the office, but the 5pm divert to staff macro didn't work. I can't see that Windows was updated today, or really any other changes at all.

宏在上午 9 点正确运行并将所有电话转移到办公室，但下午 5 点转移到员工宏不起作用。我看不到今天 Windows 更新了，或者根本看不到任何其他更改。

Has anyone seen this problem before?

有没有人见过这个问题？

回答by asc99c

OK found the problem. Looks like at some point, one of the external references for the VBA stuff was unticked (ADO 2.8 Recordset). I have added back the reference and it seems to work OK now.

OK找到问题了。看起来在某些时候，VBA 内容的外部引用之一被取消（ADO 2.8 Recordset）。我已经重新添加了参考，现在似乎可以正常工作了。

Since I've somehow got 3 upvotes for answering my own question (!), I'd better put a bit more detail in case other people are seeing this problem:

由于我以某种方式回答了我自己的问题（！），因此我获得了 3 个赞成票，因此我最好提供更多详细信息，以防其他人看到此问题：

In the Visual Basic editor, under Tools -> References, I had Microsoft ActiveX Data Objects 2.8 Libraryselected. But Microsoft ActiveX Data Objects Recordset 2.8 Librarywas unselected. Interestingly, this library doesn't even appear as an option when looking at it under Windows 7, but the macros work without it.

在 Visual Basic 编辑器中，在工具 -> 参考下，我Microsoft ActiveX Data Objects 2.8 Library选择了。但是没有Microsoft ActiveX Data Objects Recordset 2.8 Library被选中。有趣的是，在 Windows 7 下查看该库时，该库甚至不会作为选项出现，但没有它，宏也可以工作。

One more note since evidently a lot of people have this problem... My answer above did solve the problem, but only until certain people edit the file again, at which point, their version of Office automatically re-creates the problem, and I had to solve it again.

还有一个说明，因为显然很多人都有这个问题......我上面的回答确实解决了问题，但直到某些人再次编辑文件，此时，他们的 Office 版本会自动重新创建问题，而我不得不再次解决它。

There are two longer-term solutions:

有两个长期解决方案：

1) You can use late binding, and get rid of the referenced library entirely. See http://support.microsoft.com/kb/245115for more details on this.

1）您可以使用后期绑定，并完全摆脱引用的库。有关这方面的更多详细信息，请参阅http://support.microsoft.com/kb/245115。

2) For my purposes, I moved the macros into another workbook entirely - these macros should only be run from the central server anyway (people just viewing the roster won't have the ODBC data source set up, so the macros won't run anyway). So now the first step the VBA in the macro's workbook does is to open up the actual roster workbook, and it then runs the rest of the VBA code unchanged.

2）出于我的目的，我将宏完全移动到另一个工作簿中 - 这些宏无论如何只能从中央服务器运行（只是查看名册的人不会设置 ODBC 数据源，因此宏不会运行反正）。所以现在宏工作簿中的 VBA 所做的第一步是打开实际的名册工作簿，然后它会原封不动地运行其余的 VBA 代码。

回答by VPel

I do a lot of VBA work and have come across this a lot lately. I will write a program and it will run fine for a long time (for yearsin some cases) and then one day some computers start getting this error.

我做了很多 VBA 工作，最近经常遇到这个问题。我将编写一个程序，它会运行很长时间（在某些情况下会持续数年），然后有一天有些计算机开始出现此错误。

One of the more obvious things to a developer is that ADODB.Connection and/or ADODB.Recordset stop self-capitalizing themselves. Either ADODB is lowercase or the second part is. Sometimes, though, the capiltization is fine and it still happens.

对于开发人员来说，最明显的事情之一是 ADODB.Connection 和/或 ADODB.Recordset 停止自我资本化。ADODB 是小写或第二部分是。有时，虽然，capiltization 是好的，它仍然会发生。

Separating the creation of the connection and/or recordset object from the "Set new" portion code change has fixed it every time for me.

将连接和/或记录集对象的创建与“设置新”部分代码更改分开对我来说每次都修复了它。

Specifically, the following code tweaks have always fixed this for me:

具体来说，以下代码调整总是为我解决这个问题：

Change the creation of any connection objects from:

更改任何连接对象的创建：

Dim con as New ADODB.Connection

To:

到：

Dim con as ADODB.Connection
Set con = New ADODB.Connection

Likewise, change the creation of any recordset objects from:

同样，从以下位置更改任何记录集对象的创建：

Dim rs as New ADODB.Recordset

To:

到：

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

回答by Ned Bakelman

I had a similar problem where my VBA code worked fine on my local machine (Windows 7), but running it from a Citrix server (Windows 2003 Server) didn't and failed with a run time error of 430 when trying to make a connection (Set Conn = New ADODB.Connection).

我有一个类似的问题，我的 VBA 代码在我的本地机器（Windows 7）上运行良好，但是从 Citrix 服务器（Windows 2003 Server）运行它没有并且在尝试建立连接时失败，运行时错误为 430 （设置 Conn = 新建 ADODB.Connection）。

I didn't think about the differences in windows versions until reading these responses, and so when I unchecked"Microsoft ActiveX Data Objects 2.8 Library" and checked"Microsoft ActiveX Data Objects 2.7 Library", it all worked fine.

在阅读这些响应之前，我没有考虑 Windows 版本的差异，因此当我取消选中“Microsoft ActiveX Data Objects 2.8 Library”并选中“Microsoft ActiveX Data Objects 2.7 Library”时，一切正常。

Just want to pass this on and say thank you for these posts that lead me in the right direction.

只是想传递这一点，并感谢您为这些将我引向正确方向的帖子。