ADODB connection from VBA stopped working

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.

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.ConnectionSet con = New ADODB.Connection

Likewise, change the creation of any recordset objects from:

Dim rs as New ADODB.Recordset

To:

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

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.