Copy From Recordset
 

Hi

I am trying to use a recordset to create a clone of data
used in an access database within xl.

the code i have used is as follows

Sub cre_replica_merge()

Dim dbs As Database
Dim rst As Recordset
Dim sql As String
Dim xl As Object

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkJet.OpenDatabase(fname, True)
Set xl = CreateObject("excel.application")
sql = "select* from [" & tbl_name & "];"

Set rst = dbs.OpenRecordset(sql)

With xl

.Visible = True
.Workbooks.Add

h = 0
For Each fld In rst.Fields
.ActiveSheet.[a1].Offset(0, h).Value = fld.Name
h = h + 1
Next fld

.activesheet.[a2].copyfromrecordset rst

End With

rst.Close
Set rst = Nothing
dbs.Close

End Sub

When i run this code i get the following error

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

Can anyone help?

TIA

Nath