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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,711
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>
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,711
But then it's useless in Excel as you can't do anything with it?
 

rafalbballer

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

Well-known Member
Joined
Jan 24, 2012
Messages
2,711
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,222
Messages
5,623,472
Members
415,973
Latest member
johnemaile

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
Top