社区首页 >问答首页 >VBA大SQL-查询-方法'CopyFromRecordset‘的对象’范围‘失败

问VBA大SQL-查询-方法'CopyFromRecordset‘的对象’范围‘失败
EN

Stack Overflow用户

提问于 2016-01-07 01:07:00

回答 2查看 5.4K关注 0票数 0

下面发布的代码连接到oracle数据库，处理SQL查询并将结果表保存在新的工作簿中。它的工作效率高达200.000行左右。但是，对于较大的数据集，当我试图将数据从记录集对象复制到工作簿时，会出现错误方法'CopyFromRecordset‘of object 'Range’失败的：

dataWs.Range("A2").CopyFromRecordset dataset

有什么解决办法吗？我试着遍历数据集的所有元素并将它们复制到工作表中，但是对于大型数据集来说，这需要非常长的时间。你有什么想法吗？我很感激你的帮助！下面是代码：

Sub QueryExecute(sqlString, userPW, userID, serverName)
'Connect to database <serverName> using user name <userID> and 
'password <userPW> to process SQL query <sqlString> and save the
'query result in a new workbook

   Dim ConnStr As String
   Dim Cn As ADODB.Connection
   Dim dataset As ADODB.Recordset
   Dim dataWs As Worksheet
   Dim dataWb As Workbook
   Dim icols As Integer

   'Create new workbook that will hold the query result/table:
   Set dataWb = Excel.Application.Workbooks.Add
   Set dataWs = dataWb.Sheets(1)

   Application.Calculation = xlManual

   'Trim trailing/leading blanks from sqlString:
   sqlString = Trim(sqlString)

   'Create string for database connection:
   ConnStr = "UID=" & userID & ";PWD=" & userPW & ";DRIVER={Microsoft ODBC for Oracle};" _
                    & "SERVER=" & serverName & ";"

   'Connect to database:
   Set Cn = New ADODB.Connection

   On Error Resume Next 'Error handling in case connection does not work

   With Cn
     .ConnectionString = ConnStr
     .CursorLocation = adUseClient
     .Open
   End With

   'Error handling for failed connection:
   If Err.Number <> 0 Then

     dataWb.Close
     MsgBox "Connection to database failed. Check username and password."
     Exit Sub

   End If

   'Send SQL query to database:
   Set dataset = Cn.Execute(sqlString)

   'Error handling for failed query:
   If Err.Number <> 0 Then

     dataWb.Close
     MsgBox "SQL-query could not be processed."
     Exit Sub

   End If

   On Error GoTo 0

   'Copy column names in first row of table worksheet:
   For icols = 0 To dataset.Fields.count - 1
     dataWs.Cells(1, icols + 1).Value = dataset.Fields(icols).Name
   Next

   dataWs.Range(dataWs.Cells(1, 1), _
   dataWs.Cells(1, dataset.Fields.count)).Font.Bold = True 'Format column names

   'Copy data to workbook:
   '***THIS WILL FAIL FOR LARGE DATASETS***
   dataWs.Range("A2").CopyFromRecordset dataset 


   dataset.Close
   Cn.Close

   MsgBox "Query successful."

   Application.Calculation = xlCalculationAutomatic

End Sub

sql-server

oracle

vba

excel

回答 2

Stack Overflow用户

发布于 2016-01-07 01:27:23

根据微软文章 -最大行为1,048,576行和16,384列。假设，操作或仔细检查一百万行是不现实的--我们可以假设电子表格是在总结行吗？如果是这样的话--您应该始终将记录集返回到Excel的大小最小化。要做到这一点，您需要将数据的处理/汇总卸载到数据库中。

这可以在SQL查询或返回SYS_REFCURSOR的数据库过程中完成。这本质上是指向结果集的指针。

票数 1

Stack Overflow用户

发布于 2016-01-07 03:17:38

就像@OraNob说的那样，通过过滤、聚合和排序，在数据库端将返回的数据量降到最低。如果必须检索大型数据集(以减少多次调用)，则可以考虑保持记录集处于打开状态，只需将数据子集所需的数据填充到工作表中即可。如果记录集的行数超过百万行，则可以将结果写入多个工作表。

我还建议使用GetRows函数，您将需要转换它，因为GetRows数组将被标注为列然后行，并且GetRows最适合行然后列。

而且，考虑到数据集的大小，假设使用32位Office，您将无法依赖Application.Worksheet.Transpose来执行转置，因为您可能会耗尽内存，而且如果自己执行转置操作，您可能需要小心内存。考虑将转座子和插入物分成几个批次。

最后，请记住将插入作为范围执行到工作表中，因为它将比逐单元的快得多。例：

Dim aData(1 to 10000, 1 to 16)
aRecordset = rst.GetRows(10000)
'Transpose the aRecordset into aData
'...
Sheet1.Range(Sheet1.cells(1,1),Sheet1.Cells(10000,16) = aData