Excel 2007 to 2010 Issue

[GQue]

Well, I have an issue with some code that works perfectly fine in Office 2007, but since I upgraded to Office 2010, the code now fails. Oddly, it fails when calling a Sub in my code and not at a specific area in the code.

Error messge:

The object invoked has disconnected from its clients
​

Now I read several blogs that talk about Option Explicit being required (it is in my code).

Only thing I can point out is the sub being called has an ADO DB Connection in it and it is the only location in the code that calls a DB connection.

Has anyone else had this issue? I can't understand why it works in 2007, but not in 2010.

Help pls! =)

 

[GQue]

As I mentioned earlier, this worked yesterday when I was using Office 2007.

So yes, the ODBC connection is setup in Admin Tools and the Reference for Microsoft ActiveX Data Objects 2.8 Library is checked under References.

This has been in production, running every morning for 3 months without a single error while I was using Office 2007.

I do apologize if I sound snippy... I greatly appreciate your time! Just frustrated that MS has screwed me up yet again without a single note to say this may happen.

If I had the option, I would happily move back to Office 2007, but the Outlook client in 2007 has issues with embedded images in the signature and Outlook 2010 doesn't but that was a nightmare that I already dealt with... =)

 

[RoryA]

If you run the code from the VBE does it work?
As an aside, why are you opening and closing the same connection inside your loops? Why not open it once outside the loop, then deal with the data and then close it again?

 

[GQue]

No... I get the same error in the VBE.

And just never got back around to cleaning up the code... I have a snippet library that I pull from for common functions, so it was laziness. =)

Thanks for your reply RoryA

 

[RoryA]

If you comment out all the code in the called sub, does the error still occur?

 

[GQue]

Good thought Rory... I didn't think of that, but as I suspected, it still caused the error.

I deleted out all the code in the SUB that is called so the only thing there was:

Sub ClearAcctManagers()
End Sub

Same error at the same spot in the code...

 

[RoryA]

If you comment out the call, does the code work or does it simply error at a later line?

 

[GQue]

That, I did try and it completed successfully...

 

[RoryA]

If you change the name of the sub (and adjust the calling routine) does it work?

 

[GQue]

Tried that as well...

Originally it was just: ClearAM

Then I tried: Call ClearAM

Then: Call ClearAcctManagers

Then: ClearAcctManagers

=(

 

[RoryA]

Have you tried exporting, removing and reimporting the code module?