What if SQL query return more records than excel sheet row limit?

Below code works fine, but my question is: how should I update my code for the case of more than 1048570? It has never happened with more than 1048570, but theoretically the maximum number of record could be almost 4 millions (very very low possibility).

Question: I only download 5 columns(Group_number, ID, FirstName, LastName, Score) of data, if the number of record > 1048570 and <=2,000,000, then download first million record in columns 1-5, and download remaining record in columns 6-11; if more than 2 millions, .... then download first millions to columns 1-5, download 2nd million to columns 6-11, and so on. .....

Is there a way to update the code to download all records if it is indeed more than 1048570? In whatever way.

The website prevents SQL code, so I add some # in the code.
Admin's note: Our system does screen for SQL injection attacks, and is overly aggressive in doing so. A workaround is to pick a letter in the word and set the color to black. It will still look the same, but the extra color tags will change the string so it will not be screened. I've done that for you here for readability. --6StringJazzer

Post on another site: https://www.reddit.com/r/vba/comment...xcel_sheet_if/

Please Login or Register  to view this content.

Originally Posted by mjr veverka

What is about with this "Where 3 to 7" ?
Is it about the array of "GetRows" or about database ?

MS SQL database, or Sybase database.

My code is just about SQL query, not array.

We have both MS SQL and Sybase, but the code should be same, I guess.

You can just loop:

Please Login or Register  to view this content.

Originally Posted by rorya

You can just loop:

Please Login or Register  to view this content.

Thank you, your code works. I just tested your code with a smaller number than 1000000 (since I have never encountered more than one million records for the query yet).

Question 1: Does the 5 after 1000000 mean I download 5 columns/fields of data? If I want a blank column in between output data, then I should use outputcell = outputcell.offset(, 6) , correct?

Question 2: Could you please further modify your code to include field name? Currently, WB.Sheets("Download Sheet").Cells(1, 1).Offset(, j) = RecorddownloadField.Name only applies to first set of output data, how can I get this part of code into loop?

Thanks.

#1: Yes
#2: Just copy the header range from the first 5 columns each time.

You copied A1:A5 not A1:E1

Originally Posted by Haluk

You can check the attached file where the recordset is listed on Sheet2 with 5 rows of data groups

Thank you very much. By the way, what does $ mean in From [Sheet1$] ?

I've just noticed a small bug in the code. Please use the following where you can specify the maximum number of rows in the variable "MaxRows"

Please Login or Register  to view this content.