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=JDEEXTRACT"
' 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
Problems with DAO and SQL Server
DBArchitect
Since our support team can't help out with this, I did some research and found this Office newsgroup that might be able to help answer your question. http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US
This is the Office Excel programming newsgroup and there must be people there that have come across this situation before. I hope someone there can help you track down a solution.
-brenda (ISV Buddy Team)
Martina23809
Hi Brenda
Unfortunately I do not have a copy of Office 2003 at the site where SQL Server is running (they are too mean to upgrade).
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
erb
unfortunately, since Excel 97 is no longer supported, our support engineer won't be able to assist... but he did ask if the same issue can be reproduced in Excel 2003. if so, he might be able to come up with a solution that you can try out on the older version.
-brenda (ISV Buddy Team)
bubu
Thanks for the reply Derek,
I limited the query to a few fields that I know are either numeric or text (field type 18 or 20 cannot relocate the translation of these, saw it yesterday somewhere), I still have the same error.
The table only contains types 18, 20 and 23 - I think theses are Text, Long and Date.
If its necessary I will have to work through cell by cell and write in the data by scrolling through the recordset, but from experience this takes a long time ( 12 columns by 12500 rows)
gssi
Hello ADG,
Had a look at the CopyFromRecordset method and it says "If the Recordset object contains fields with OLE objects in them, this method fails." but it doesn't give any indication of the error code for the method failure.
Would that be the reason