Solved: Class does not support Automation

[Movian]

Hey,
my database has been fine for a very long time but suddenly my ado record set references are now generating the following error.

"Class does not support Automation or does not support expected interface."

I have attempted to refresh all of the references in my db including the Microsoft Activex data objects 2.6 reference.

however the problem still persists. The problem only appears on other machines not on my machine (which is another reason i am looking into the references).

Any help is appreciated.

[OBP]

Movian, have those computers had any MS downloads lately?
I seem to be seeing quite a few of these sudden Access VBA problems where they work OK and then stop, but not on all machines. It usually involves lost or changed .dll files etc.

[Movian]

no, they are all running Microsoft access Runtime 2007 (or 2010)

I managed to find a version from the day before it started happening and this Version does not have the problem. I have also realized that i updated MY computer on the morning it started happening with windows 7 SP1. although i did release 1 update after installing the update. However the second had the problem. I double checked the references and they apear fine.....


Im stumped but this is a huge problem as it means i can't release updates till i figure out what the problem is ...

[OBP]

This thread suggsets Compiling the VBA fixes it.
http://bytes.com/topic/visual-basic/answers/668805-class-does-not-support-automation-does-not-support-expected-interface

[OBP]

This one says it is the objects
http://www.makebarcode.com/info/appnote/app_017.html

[OBP]

This one says a MDAC dll
http://www.dbforums.com/visual-basic/1632718-class-does-not-support-automation-does-not-support-expected-interface.html

[Movian]

I always debug and compile my VBA code before i convert to an ade then rename to accdr.

Also its referencing the same version of the Active X data Objects that always has and if i send an older version of the DB to the client it works fine. Its like there is a persistent corruption in the DB that i can't get rid off.

I am going to try and see if removing the reference the Microsoft Active X Data Objects 2.6 and changing the code to late binding will resolve the issue. But it has been working this way for 6-8 months with no issue.... that's what stumps me.

[Movian]

Well its not an ideal fix but i ran out of time and just needed to get this resolved.

I took a backup and updated it with the changes made in he last 24 hours and it now appears to be working correctly. I'm guessing it was some type of corruption issue that was staying in the forms or modules so even creating a new DB was not fixing it...

Will give it another 24 hours then mark resolved....
Mabye this will be the straw to kill our access front end to the SQL database.... would be better to just make it in vb6 over access.....

[Movian]

well the problem appears to have returned

i am now officially stressed out.

:( :banghead:

[Tommy]

Movain,
Can you upgrade to the 2.8 version of the active x? I am guessing but if you are using 2.6 and the latest is 2.8, may be a dll hell issue and this "could" get you around it.

I also see that some of the dll's are not registered correctly from other posts witht the same problem.

I don't have Access 2010 or I would offer to help in the debugging. I do have SQL 2005 with a developers License.

[stanl]

This probably won't help but I have Ofice 2010, and my Access 2010 app does not contain any vba code - as I reference everything from an external .exe, but still use ADO.

I had general issues with things not working after SP1 was installed (I had to run Mountvol /E from the command line to even get it to work). However, after a thorough defrag [MyDefrag v. 4.31] all was corrected. SP1 is a [by comparison] efficient upgrade, but a good defrag is recommended. just .02 Stan

[Movian]

I have attempted changing the version through all the available iterations of ADO. 2.0 - 3.6 none seem to resolve the problem.
I have tried pulling everything into a new database same problem..

"STANL" I defrag my driver every night but that has done nothing to help

Only thing that seemed to fix it was to use a backup and redo the work since then however a couple hours after that the problem arises again...

its the myrs.open "SELECT * FROM TABLE", currentproject.connection, key set, optimistic

Thats generating the error....

i have both DAO and ADO references but i specifically define them

dim myrs as new adodb.recordset

and dim myrs as dao.recordset.

So that shouldn't be a problem.

[Movian]

Well looks like its my machine....

Sent the backup to the terminal server and worked on it there. Not only does it not generate the error when i use the backup on there and redo the work but also if i move over the .adp from my machine that compiles to a corrupt version it shows the error in code....

Looks like my machine is doing something strange with the reference locations.. the only thing i can think that would cause this is my upgrade to Windows 7 SP1...

So i would note to others to be careful with their references if they too are upgrading to SP1 on windows 7.

Guess i have a rebuild to schedule now.... FUN!