8

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?

asc99c
  • 3,815
  • 3
  • 31
  • 54

3 Answers3

20

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.

asc99c
  • 3,815
  • 3
  • 31
  • 54
3

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.

Kip Bryan
  • 461
  • 4
  • 6
Ned Bakelman
  • 31
  • 1
3

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:

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
VPel
  • 413
  • 5
  • 12