I am trying to return a DAO recordset from SQL Server so that I can use
CopyFromRecordSet to populate some data in Excel 97. The below creates the
recordset but the CopyFromRecordSet fails with :

error -2147467259 (80004005) Operation is not supported for this type of
object

If I change to another database (SpecData) and change the strSQL to select
a table without the owner prefix the code works. The permissions on the two
databases are identical. Can anyone point me in the right direction?



Function OpenConnection() As Boolean
Dim wrk As DAO.Workspace
Dim cnn As DAO.Connection
Dim rst As DAO.Recordset
Dim strConnect, strSQL As String

On Error GoTo Err_OpenConnection
' Create connnect string.
strConnect = "ODBC;DSN=JDEEXTRACT;UID=ADG;PWD=;DATABASE=JDEEXTR ACT"
' Create SQL string.
strSQL = "SELECT * FROM dbo.XF4111;"

' Create ODBCDirect workspace.
Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "ADG", "", dbUseODBC)
' Open connection.
Set cnn = wrk.OpenConnection("MyCon", dbDriverNoPrompt, True, strConnect)

' Open recordset on connection.
Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset)
On Error GoTo 0
Worksheets("Sheet1").Range("A2").CopyFromRecordset rst
Debug.Print rst.RecordCount
OpenConnection = True

Exit_OpenConnection:
rst.Close
cnn.Close
wrk.Close
Set rst = Nothing
Set cnn = Nothing
Set wrk = Nothing
Exit Function

Err_OpenConnection:
MsgBox "Error " & Err & ": " & Err.Description
OpenConnection = False
Resume Exit_OpenConnection
End Function

Also I did find a kb article which shows how to copy the recordset to an
array, then transpose it to copy into a range of a equal size (code works
for all versions of office from 97 onwards). This also failed. I tried to set
one cell to the value of one element in the array, this failed. The array in
the example was stored in a variable declared as variant. If I changed the
code from

Worksheets("Sheet1").cells(2,1).value=v(1,1)

to

Worksheets("Sheet1").cells(2,1).value=trim$(v(1,1) )

the code worked. The Data in the element was packed with spaces eg "ABC
". By looking at the field type of each field I am able to assign the cell
values using either the val() or trim$() functions, without these the
assignment fails. Is there a problem with assigning data of type variant to
cells / ranges?


--
Tony Green