CopyFromRecordset Run-time error 430:

[Guest]

Hi TWIMC

The error message that I get initiates on the code line
rge.CopyFromRecordset rst. The error message returned is 'Run-time error 430:
Class does not support Automation or does not support expected interface.'
The strange thing is that this procedure has been working on all users for
several months I've just set up a new user but it doesn't work on her
machine. I've check the references within Access and Excel on the selected
references and they are all the same version as my own settings. I've also
checked the data within the two queries and they return correct outputs with
no OLE Objects. Any ideas on how to resolve this issue would be much
appreciated.

Dim xlApp As New Excel.Application
Dim wbk As Workbook
Dim ws As Worksheet
Dim rge As Range
Dim rst As DAO.Recordset
Dim fld As DAO.Field

DoCmd.Hourglass True
Select Case strOpenArgs
Case "BANKER"
Set rst = CurrentDb.OpenRecordset("qselREPORT_BASE_BANKER",
dbOpenSnapshot)
Case "DEAL"
Set rst = CurrentDb.OpenRecordset("qselREPORT_BASE_DEAL",
dbOpenSnapshot)
End Select

Set wbk = xlApp.Workbooks.Add
Set ws = wbk.Sheets.Add
Set rge = ws.Range("A2")
ws.Name = "DATA"

For Each fld In rst.Fields
ws.Cells(1, fld.OrdinalPosition + 1) = fld.Name
Next

rge.CopyFromRecordset rst
xlApp.ActiveWindow.Zoom = 85
ws.Range("A2").Activate
xlApp.ActiveWindow.FreezePanes = True

For Each ws In wbk.Worksheets
If ws.Name <> "DATA" Then wbk.Worksheets(ws.Index).Delete
Next

DoCmd.Hourglass False
xlApp.Visible = True

On Error Resume Next
rst.Close
Set rst = Nothing
Set wbk = Nothing
Set ws = Nothing
Set rge = Nothing

End Function

TIA
KM

 

[OfficeDev18 via AccessMonster.com]

Check the references again, against a working machine. The references don't
only have to be there, they must be in the same order of priority. Her
machine may have the references in different order to priority.

HTH

 

[Guest]

Hi

I can't see the order of references having any relevance to the error, is
the an MSDN web site that highlights this issue of having reference in a
preference order?

TIA
KM

 

[Guest]

In addition the references are in alphabetical order so you can sort them
anyway!

 

[OfficeDev18 via AccessMonster.com]

I'm not sure if you and I are referring to the same references. With the new
user's VBA editor open, click on Tools-->References. The order of references
is critical, as this determines the order that Access tries to fulfill the
VBA commands. For example, if you use DAO objects/code and you have the ADO
reference above the DAO reference, your program will bomb. This is why
there's a huge 'UP' and 'DOWN' arrow in the dialog box, to position the
references precisely where you need them. That's why I'm telling you to check
her ref's against a known working machine. Her reference order may not match,
and that's a possible reason for your problem.

In addition the references are in alphabetical order so you can sort them
anyway!

Check the references again, against a working machine. The references don't
only have to be there, they must be in the same order of priority. Her

[quoted text clipped - 64 lines]