|
最近,做报告要用到oracle的实际数据,由于数据量全取的话数量量比较大,Excel office2013 32位,无法全部取出超过100万行,即使分表取出,内存也不足。
如何根据要要求请最近一年的数据?
- Public Sub myors()
- Dim cnn As New ADODB.Connection
- Dim rs As New ADODB.Recordset
- Dim cnnStr As String, myTable As String, SQL As String, i As Integer, time365 As Date
- myTable = "T_APMR515" '指定数据表
- '设置建立与Oracle数据库服务器中连接的字符串
- time365 = Format(Now - 20, "yyyy-mm-dd")
- cnnStr = "Provider=MSDAORA;" _
- & "Data Source=dw1;" _
- & "User ID=u051028;" _
- & "Password=welcome123#;"
- 'oadb是oracle服务器名;
- cnn.ConnectionString = cnnStr
- cnn.Open
- '创建查询记录集
- SQL = "select * from " & myTable & " WHERE rvv09 >=' & time365 & ' "
- MsgBox SQL
-
- rs.CursorLocation = adUseClient '游标改为客户端游标
-
- rs.Open Source:=SQL, ActiveConnection:=cnn
-
- With rs
- For i = 1 To .Fields.Count '复制字段名
- Cells(1, i) = .Fields(i - 1).Name
-
- Next i
- Range("A2").CopyFromRecordset rs '复制记录数据
- .Close '关闭记录集
- End With
- cnn.Close
- Set rs = Nothing
- Set cnn = Nothing
- End Sub
-
复制代码 SQL = "select * from " & myTable & " WHERE rvv09 >=' & time365 & ' " 这句该如何改?
|
|