Excel Vba-SQL retrieve more than 1m rows.

[rafalbballer]

Hi
i am using the code below to retrieve the data from SQL server database.
The only problem is when query gives me more than 1milion rows so not all the rows are imported into sheet.
Is there any workaround for this ?

thanks

Dim conn As ADODB.Connection 
    Dim rs As ADODB.Recordset 
    Dim sConnString As String 
    Sheets("Projects").Activate 
    
    ' Create the connection string. 
    sConnString = "Provider=SQLOLEDB;Data Source=moj server;" & _ 
                  "Initial Catalog=Datasources;" & _ 
                  "User ID=nazwa usera;" & _ 
                  "Integrated Security=SSPI;" 
    
    ' Create the Connection and Recordset objects. 
    Set conn = New ADODB.Connection 
    Set rs = New ADODB.Recordset 
    
    ' Open the connection and execute. 
    conn.Open sConnString 
    conn.CommandTimeout = 20000 
       Set rs = conn.Execute("kwerenda") 
    ' Check we have data. 
    ' Check we have data. 
    If Not rs.EOF Then 
        ' Transfer result. 
        Sheets("Projects").Range("A2").CopyFromRecordset rs 
    ' Close the recordset 
        rs.Close 
    Else 
        MsgBox "Error: No records returned.", vbCritical 
    End If 

    ' Clean up 
    If CBool(conn.State And adStateOpen) Then conn.Close 
    Set conn = Nothing 
    Set rs = Nothing

 

[Kyle123]

There's nothing you can do about that, you can only put 1mill rows on a sheet.

Your alternatives are:
<ul>
<li>Summarise your data in SQL so you only bring in what you need</li>
<li>Have a look at Power Pivot on the assumption that it is available to you</li>
</ul>

 

[rafalbballer]

maybe there is a way to retrieve the data which does not fit to next sheet ?

 

[Kyle123]

But then it's useless in Excel as you can't do anything with it?

 

[rafalbballer]

Kyle what do You mean useless ?

I would like to have one part of query results in one sheet and the rest of it in another sheet.
That would work for me perfect

 

[Kyle123]

But Excel can't use that for anything useful, you can't use a pivot table like that, you can't write efficient formulas that span multiple sheets like that - it will be far from perfect.

You'd be much, much better off aggregating in SQL and only bringing back what you need - you aren't seriously going to go through millions of lines one by one.

However, if you do want to do this, you really need to change your sql, presumably you are executing a stored procedure that returns more than a million rows, you need to make it take an extra parameter that gives it a start row and an end row, then loop.