CopyFromRecordset -> Run-time error on some machines

[VBAinspire]

I am trying to use the CopyFromRecordset action to transfer an Access recordset to Excel. It works on some machines but not on others. On some machines, the code crashes with the following error:

Run-time Error 430: Class does not support Automation or does not support expected interface.

This is the test funtion I wrote to test the CopyFromRecordset action (xlBook and xlApp declared in main function):

Private Function RunTest5() As Boolean
'Test 5 - Copy Recordset/Close Excel
On Error GoTo Crash
Dim rstTest As ADODB.Recordset
Set rstTest = New ADODB.Recordset
rstTest.Open ("Select * from HELP&quot, _
CurrentProject.Connection, adOpenStatic, adLockReadOnly
xlBook.Worksheets(1).Range("A1&quot = "Recordset Test to Excel"
xlBook.Worksheets(1).Range("A3&quot.CopyFromRecordset rstTest
rstTest.Close

xlBook.Close False
xlApp.Quit

RunTest5 = True
Exit Function
Crash:
RunTest5 = False
MsgBox "Test 5 Error: " & Err.Number & " - " & Err.Description
End Function

Any ideas on what could be causing the problem and how to fix it?

 

[glennho]

Somebody else can back me up on this, but from your VB Editor, I'd look at References - check your Microsoft ActiveX Data Objects #.# Library. If your code is using 2.7 and users only have 2.5, it could choke. If you choose an earlier library, like 2.5, it should still work for you, and work for users running earlier versions as well.
That's been my experience, anyway, when we were in transition between Office9 and 10.
Good luck -
- gh

 

[Jables]

VBAinspire,

Did you ever figure out how to correct this problem? I am getting the exact same error. I wrote a function that uses the CopyFromRecordset method to transfer data from access to excel. It worked for months on end and then my boss got a new laptop. The function breaks only on his machine with that annoying "Run-time Error 430: Class does not support Automation or does not support expected interface.
" error.

I have made sure the references on his machine are the same references on all the machines on which the function works.

 

[Jables]

As an update, I fixed the problem by changing the CursorLocation property of the recordset from adUseServer to adUseClient.