来自VBA的ADODB连接停止工作 [英] ADODB connection from VBA stopped working

问题描述

我们有一个电子表格，用于管理不合时宜的支持工程师。一段时间后，我添加了一些VBA代码，它会在几小时内自动将支持电话号码转移到工程师的手机。

它通过连接到数据库运行通过电话提供商，并更新转移号码。

今天下午，中央服务器突然不起作用：

  Dim Db As ADODB.Connection 
 
 Sub ConnectDatabase（）
设置Db =新建ADODB.Connection 
 Db.Open SupportMobileDb
 End Sub 
  

代码停在新ADODB 。连接行，并报告：

 运行时错误'430'：
类不支持自动化或不支持预期的
接口
  

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

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

有没有人看到这个问题？

解决方案

确定发现问题。看来，在某些方面，VBA的外部参考之一是未经验证的（ADO 2.8 Recordset）。我已经添加了参考，现在似乎可以正常工作。

由于我以某种方式有3个upvote来回答我自己的问题（！），我最好放一些更多的细节，以防其他人看到这个问题：

在Visual Basic编辑器中，在工具 - >参考，我有 Microsoft选择了ActiveX数据对象2.8库。但是， Microsoft ActiveX数据对象Recordset 2.8库已被取消选择。有趣的是，这个图书馆甚至在Windows 7下看起来并不是一个选项，但宏没有使用它。

另外一个注意事项显然是很多人们有这个问题...我上面的答案解决了这个问题，但直到某些人再次编辑文件，在这个时候，他们的Office版本会自动重新创建问题，而且我不得不再次解决。 p>

有两个长期的解决方案：

1）你可以使用后期绑定，并摆脱引用图书馆完全有关详细信息，请参阅 http://support.microsoft.com/kb/245115 。

2）为了我的目的，我将宏完全移动到另一个工作簿中 - 这些宏只能从中央服务器运行（人们只要查看名册就不会有ODBC数据源设置，所以宏不会运行）。所以现在，宏工作簿中的VBA的第一步是打开实际的名册工作簿，然后运行其余的VBA代码。

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?

解决方案

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 Library selected. But Microsoft ActiveX Data Objects Recordset 2.8 Library was 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连接停止工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！