Excel Vba-SQL retrieve more than 1m rows.

rafalbballer

New Member
Joined
Aug 12, 2013
Messages
19
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


Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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>
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top