CopyFromRecordset funciton object interface automation error Class does not support Automation

After searching over 100 posts, this problem happens in DAO and ADO. - Why does only one machine fail when others work

This instance is running from Access 2007. Post show this issue happens in other applications as well.

Please note: The code consistantly works on some machines (or servers) but errors on others.

Failure line:  ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset strData
ObjXL is a Object Varable - for Excel 2010    

This seems to be a long-time problem that still exist with no real solution. After reading over 100 post, this one comes closest to a solution. But, it is not really a great solution. The error is on a new Windows 2003 Server. The old Windows 2003 Server works just fine.


We have a ton of code for Access using VBA automation for Excel. It works perfectlly on over 20 independent workstations.
It worked on our former citrix server just fine over the last year. Recentlly, a new citrix server was set up. The configuration appears to be identical.
The only problem on the new citrix server is the CopyFromRecordset statement error
My error trapping and custom logging function has isolated the error to this line of code. The error description is:
       Class does not support Automation or does not support expected interface

How can this line of code work on dozens of workstations (and a server) and not on the next installation?
There are a lot of post out there looking for an answer for Office 2007 (and before) with no answers.

Some suggested solutions tried that did not work:

Re-register the DAO   e.g.   Regsvr32.exe "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.DLL"
B.T.W. code returning the recordcount from the data object a few lines before worked perfectlly

Re-Install Office 2007 SP2 -  B.T.W. The Excel automation for adding new worksheet, and adding text using the ObjXL previous to the copyfromrecordset works perfectlly.

Review Object Reference in Tools menu: As mentioned - the previous code references the Excel object and does several task before the copyfromrecordset is called


 

 

Question Info


Last updated October 4, 2018 Views 1,530 Applies to:

Here is something I will add to the "tried" list.

Quote:

Thanks for that. But I've been seaching whilst waiting for an answer and then I found this on Allen Browne's site:
Re-register the lib:
regsvr32 "c:\program files\common files\microsoft shared\dao\dao360.dll"
Occasionally, the problem is not solved until you unregister the library and re-register it. Uncheck the missing library in Access. Close Access. Issue this command, and then the one above to re-register it:
regsvr32 -u "c:\program files\common files\microsoft shared\dao\dao360.dll"     That worked!
End Quote


Previously we re-registered the DLL with no luck.

Access Programming relic

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Would highly suggest reviewing the Library Table in the web site.

http://allenbrowne.com/ser-38.html

The solution above worked for me. Registering alone was reported at one other web site. But, in my case it required uninstalling and reinstalling.

What surprises me is how only one function in a class library was affected. But, after reading post after post on the internet, it appears to be something not all that uncommon.

 

The CopyFromRecordset is an Excel library function. However, there are sites that discuss DAO, ADO, Interop, Oracle, and others that had this error. Evidently, there is some relationship.

It appears that many have problems. Almost everyone gets distracted with the code, the References, and other nonsence. When an application works on some machines but not on a single one, it is a giveaway.

 

Another site discussed a different "build" of the same DLL. And, they claimed this fixed thier problem.

Another site trapped the error, reran a copy of the same code and claimed it worked (but they didn't like that solution of course).

Access Programming relic

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.