Runtime error when running SQL query in Excel VBA

Question

I am running an SQL query on an oracle DB, it is just a simple select * from table query. This works fine for a small table 900 rows and 5 columns. But when I try it on a table with say 30 columns and 95,000 rows I get the below error. I tried increasing MaxRecords but to no avail.

Error Code

Public Const sConnect As String = "Driver={Microsoft ODBC for
 Oracle};Server=server;Uid=user;Pwd=password"
Sub GetData1()
  i = 0
  Sheets(1).Range("a1:ao20000").ClearContents

  Dim rsConnection As ADODB.Connection
  Dim rsRecordset As ADODB.Recordset
  Dim sQuery As String

  sQuery = "select * from trade"

  Set rsConnection = New ADODB.Connection
  Set rsRecordset = New ADODB.Recordset

  rsConnection.ConnectionString = sConnect
  rsConnection.Open
  rsRecordset.MaxRecords = 1048575

  Set rsRecordset = rsConnection.Execute(sQuery)

  Worksheets(1).Range("A2").CopyFromRecordset rsRecordset

  For i = 0 To rsRecordset.Fields.Count - 1
    Worksheets("Sheet1").Cells(1, i + 1).Value = rsRecordset.Fields(i).Name
  Next i

  rsConnection.Close
  Set rsConnection = Nothing
  Set rsRecordset = Nothing
End Sub

Regards, Tim

Tim · Accepted Answer · 2017-07-14 14:43:14Z

1

After a little more digging I found the answer. It relates to database fields which are TIMESTAMP(6). I have to redo my db query to do a to_char(timestamp_field) and all is OK.

Thanks Tim

answered Jul 14, 2017 at 14:43

Tim

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Runtime error when running SQL query in Excel VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related