CopyFromRecordset Causing Excel to Crash

Question

I have created a connection from Oracle (64 bit) to Microsoft Excel 2013 (64 bit). The VBA connects as expected and pastes the column headers as expected, but once I try to use the CopyFromRecordSet Excel freezes before copying the records. I'm not sure why. I don't get any error messages.

These are the reference libraries that I have selected:

1. Visual Basic for Applications
2. Microsoft Excel 15.0 Object Library
3. OLE Automation
4. Microsoft Office 15.0 Object Library
5. Microsoft Forms 2.0 Object Library
6. Microsoft ActiveX Data Objects 6.1 Library
7. TabBtnEx 1.0 Type Library
8. Microsoft ActiveX Data Objects Recordset 2.8 Library

Here is the code:

Sub getdata3()
Dim UserName As String
Dim PassWord As String
Dim con As Object
Dim recSet As Recordset
Dim SQL_String As String
Dim dbConnectStr As String
Dim recordCount As Long
Dim col As Integer

'Set up variables
UserName = "unid"
PassWord = "pwd"
dbConnectStr = "DSN=DW;Uid=" & UserName & ";Pwd=" & PassWord


'Set connection parameters
Set con = New ADODB.Connection
Set recSet = New ADODB.Recordset
con.ConnectionString = dbConnectStr
con.ConnectionTimeout = 30
con.Open dbConnectStr

'Query
SQL_String = "SELECT * FROM U0408224.D_FLAG WHERE DWID = 675863"
recSet.Open SQL_String, con, adOpenStatic

'Recordset to sheet
col = 0
Do While col < recSet.Fields.Count
Sheets("SQL").Cells(20, col + 1) = recSet.Fields(col).Name
col = col + 1
Loop
Sheets("SQL").Cells(21, 1).CopyFromRecordset recSet

'Close recordsets and connections
recSet.Close
con.Close
Set con = Nothing
Set recSet = Nothing


End Sub

Answer 1

I think I figured it out. I changed the CursorTypeEnum from adOpenStatic to adOpenForwardOnly and it worked perfectly. I suspect that there is some type of error in the ODBC connection that doesn't permit the adOpenStatic. I am running Excel 64 bit, however, the dll file that is used for the ODBC connection had 32 in it somewhere.