VBA ADODB Connection Fails

Question

We have an Exccel spreadsheet used to manage rota for timeless engineers. Some time ago, I added some VBA code that automatically sends support phone numbers to an engineering phone from a watch.

He does this by connecting to a database run by phone providers and updates the forwarding number.

This afternoon it does not work 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 line New ADODB.Connection 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 correctly to the data source. It will no longer work through VBA on this server.

The macro worked correctly at 9am and redirected all the phones to the office, but switching 5pm to the staff macro did not work. I don’t see that Windows has been updated today or really any other changes at all.

Has anyone seen this problem before?

Answer 1

OK found the problem. It appears that at some point one of the external references to the VBA material was rejected (ADO 2.8 Recordset). I added a link and it seems to be working fine now.

Since I somehow had 3 upvotes to answer my own question (!), I would rather put a little more if other people see this problem:

In the Visual Basic editor, under Tools → Links, I selected Microsoft ActiveX Data Objects 2.8 Library . But Microsoft ActiveX Data Objects Recordset 2.8 Library not selected. Interestingly, this library does not even appear as an option when viewing it under Windows 7, but macros work without it.

Another remark, since obviously many people have this problem ... My answer above solved the problem, but only until some people edit the file again, at which point their version of Office automatically recreates the problem, and I had to solve it again.

There are two long-term solutions:

1) You can use late binding and completely get rid of the specified library. See http://support.microsoft.com/kb/245115 for more details.

2) For my purposes, I moved the macros to another book completely - these macros should only be run from the central server (people just looking at the list will not have an ODBC data source, so the macros won anyway). So, now the first step that VBA does in the macro book is to open the real registry book, and then the rest of the VBA code remains unchanged.

Answer 2

I work a lot in VBA and have come across this many times lately. I will write a program, and in some cases it will work fine (for years ), and then one day some computers will start to receive this error.

One of the most obvious things for the developer is that the ADODB.Connection and / or ADODB.Recordset ceases to bother itself. Either ADODB has a lowercase case, or the second part. Sometimes, however, capitalization is good, and this is still happening.

Separating the creation of the connection object and / or set of records from the change in the "Install New" code, it fixed it every time for me.

In particular, the following code settings always fixed this for me:

Change the creation of any connection objects:

 Dim con as New ADODB.Connection 

To:

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

Similarly, modify the creation of any recordset objects from:

 Dim rs as New ADODB.Recordset 

To:

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

Answer 3

I had a similar problem when my VBA code worked fine on my local computer (Windows 7), but it did not run from the Citrix server (Windows 2003 Server) and did not run with a 430 runtime error when trying to connect (Set Conn = New ADODB.Connection).

I did not think about the differences in the versions of Windows until I read these answers, so when I do not check "Microsoft ActiveX Data Objects 2.8 Library" and check "Microsoft ActiveX Data Objects 2.7 Library", everything works fine.

I just want to convey this and say thanks for these messages that lead me in the right direction.