来自 VBA 的 ADODB 连接停止工作
我们有一个 Excel 电子表格，用于管理非工作时间支持工程师的轮值表。不久前，我添加了一些 VBA 代码，该代码可以在非工作时间自动将支持电话号码转移到工程师的电话上。
它通过连接到电话提供商运行的数据库并更新转移号码来实现此目的。
今天下午，它突然在中央服务器上无法工作：
Dim Db As ADODB.Connection
Sub ConnectDatabase()
Set Db = New ADODB.Connection
Db.Open "SupportMobileDb"
End Sub
代码停在 New ADODB.Connection 行，并报告：
Run-time error '430':
Class does not support Automation or does not support expected
interface
我仍然可以在我的笔记本电脑上运行宏，并且它工作正常。在中央服务器上，我仍然可以使用 Excel，并正确连接到数据源。它只是无法再通过此服务器上的 VBA 工作。
该宏在上午 9 点正确运行，并将所有电话转移到办公室，但下午 5 点转移到员工宏不起作用。我看不出 Windows 今天有更新，也看不出有任何其他变化。
以前有人见过这个问题吗？
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.
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.Connection line, and reports:
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.
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.
Has anyone seen this problem before?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好的找到问题了。看起来在某个时刻，VBA 内容的外部引用之一未被选中（ADO 2.8 Recordset）。我已经添加回参考，现在似乎工作正常。
由于我以某种方式获得了 3 票回答我自己的问题 (!)，我最好提供更多详细信息，以防其他人看到此问题：
在 Visual Basic 编辑器中，在“工具”->“工具”下。参考资料，我选择了
Microsoft ActiveX Data Objects 2.8 Library。但Microsoft ActiveX Data Objects Recordset 2.8 Library未被选中。有趣的是，在 Windows 7 下查看该库时，该库甚至不作为选项出现，但宏在没有它的情况下也可以工作。还有一点要注意，因为显然很多人都有这个问题...我上面的回答确实解决了问题，但只有在某些人再次编辑文件之前，此时他们的 Office 版本会自动重新创建问题，我只好再次解决。
有两种长期解决方案：
1）您可以使用后期绑定，并完全摆脱引用的库。有关详细信息，请参阅 http://support.microsoft.com/kb/245115。
2）出于我的目的，我将宏完全移动到另一个工作簿中 - 这些宏无论如何都只能从中央服务器运行（只是查看花名册的人不会设置 ODBC 数据源，因此宏不会运行反正）。因此，现在宏工作簿中的 VBA 执行的第一步是打开实际的花名册工作簿，然后不加更改地运行其余的 VBA 代码。
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.
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:
In the Visual Basic editor, under Tools -> References, I had
Microsoft ActiveX Data Objects 2.8 Libraryselected. ButMicrosoft 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.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.
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/245115 for more details on this.
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.
我做了很多 VBA 工作，最近也经常遇到这个问题。我将编写一个程序，它会运行很长一段时间（在某些情况下几年），然后有一天，一些计算机开始出现此错误。
对于开发人员来说，最明显的事情之一是 ADODB.Connection 和/或 ADODB.Recordset 停止自我大写。 ADODB 要么是小写，要么第二部分是小写。但有时，大写字母没问题，但这种情况仍然会发生。
将连接和/或记录集对象的创建与“设置新”部分代码更改分开，每次都为我解决了这个问题。
具体来说，以下代码调整始终为我解决了这个问题：
将任何连接对象的创建从：
更改为：
同样，将任何记录集对象的创建从：
更改为：
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 years in some cases) and then one day some computers start getting this error.
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.
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:
To:
Likewise, change the creation of any recordset objects from:
To:
我遇到了类似的问题，我的 VBA 代码在本地计算机 (Windows 7) 上运行良好，但从 Citrix 服务器 (Windows 2003 Server) 运行它却无法运行，并且在尝试建立连接时失败并出现运行时错误 430 （设置 Conn = 新 ADODB.Connection）。
在阅读这些回复之前，我没有考虑 Windows 版本之间的差异，因此当我取消选中“Microsoft ActiveX Data Objects 2.8 Library”并选中“Microsoft ActiveX Data Objects 2.7图书馆”，一切正常。
只是想传递这个信息并感谢您的这些帖子引导我走向正确的方向。
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).
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.
Just want to pass this on and say thank you for these posts that lead me in the right direction.