Connecting to Oracle 10g with ODBC from Excel VBA

Question

Hi, The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes Excel. Any ideas?? Thanks

Sub GetData()
    Dim conn As New ADODB.connection    
    Dim connString
    connString = "DSN=name;Uid=user;Pwd=pass"
    Dim rsRecords As New ADODB.recordSet

    conn.Open connString
    rsRecords.CursorLocation = adUseServer
    rsRecords.Open "select * from xxx", conn, adOpenForwardOnly, adLockReadOnly

    If conn.State = adStateOpen Then
      MsgBox rsRecords.RecordCount
    Else
       MsgBox "no connection"
    End If

    rsRecords.Close
    Set rsRecords = Nothing
    conn.Close
    Set conn = Nothing
End Sub

Answer 1

If you want Recordcount to work, especially without fetching the records, you need to use adUseClient not adUseServer

If this is a large operation and you'll be doing it frequently, though, you should really select the count() instead and make the database do the counting for you. With adUseClient ADO will silently fetch all the records into your application's memory whether you like it or not.

Answer 2

It seems Excel was crashing because it cannot handle some Oracle datatypes. The select * was returning a column of type TIMESTAMP(3) WITH TIME ZONE. This would cause Excel to crash as soon as I tried to read from the recordset.