在Excel VBA中运行SQL查询时的运行时错误
问题描述:
我在oracle数据库上运行SQL查询,它只是一个简单的select * from表查询。这适用于小型表格900行和5列。但是当我在有30列和95,000行的表上尝试时,我得到了下面的错误。我试图增加MaxRecords,但无济于事。在Excel VBA中运行SQL查询时的运行时错误
Error 代码
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
问候, 添
答
多一点挖后,我找到了答案。它涉及到TIMESTAMP(6)的数据库字段。我必须重做我的数据库查询做一个to_char(timestamp_field),一切都OK。
谢谢 Tim