Problems with DAO and SQL Server

[ADG]

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

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?

 

[aidan.heritage]

Possibly messy, but it would surely work, move to the first record then
write one record at a time to the database, moving to the next record
until EOF is reached?

 

[ADG]

Hi Aidan

Yes, I can work through the Recordset and post each field, the average size
of recordset returned by my query will be 20,000 records by 21 fields. This
is slow, the copyfromrecordset and array copy methods are significantly
faster. I was hoping that someone else might of found this problem and a
simple solution.

Regards

 

[aidan.heritage]

Just a thought, but the knowledge base article said an array of equal
size - given that you know the number of fields (columns) and the
number of records (rows) why not use these values to get the actual
size of the array and set the entire value at this point?

 

[ADG]

Tried this, the code to resize the range was included. I think the problem is
that Excel fails to interpret the data types correctly.

 

[Tim Williams]

What are the column datatypes in the table you're querying?
Is there one particular column which causes the problem? If so (maybe the space-padded one?) you could try casting the type in the
SQL or using TRIM() in your query (if available, not familiar with SQL Server)

 

[ADG]

Types as returned by field type are 18, 20 and 23. The data is either a
String, Long or Date/Time. Will look at what is possible in the SQL string
sent to MS SQL SERVER. I am not familar with what can be done on this
platform.

Regards