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