ADODB connection from VBA stopped working

Question

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?

Answer 1

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.