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/

Set RecordCountRs = CreateObject("ADODB.Recordset")
    RecordCountQuery = "Select COUNT(*) From Table_ABC WHERE Group_number = " & InputGroupNumber  'InputGroupNumber is integer
    RecordCountRs.Open RecordCountQuery, ConnectionString
    
    WB.Sheets("Summary").Cells(1, 2).Value = RecordCountRs.Fields(0).Value
    
    Set RecordCountRs = Nothing
    
    If WB.Sheets("Summary").Cells(1, 2).Value <= 1048570 Then
        Set RecordDownloadRs = CreateObject("ADODB.Recordset")
        RecordDownloadQuery = "Select Group_number, ID, FirstName, LastName, Score From Table_ABC Where Group_number = " & InputGroupNumber 
        RecordDownloadRs.Open RecordDownloadQuery, ConnectionString
        
        j = 0
        For Each RecorddownloadField In RecordDownloadRs.Fields
             WB.Sheets("Download Sheet").Cells(1, 1).Offset(, j) = RecorddownloadField.Name
             j = j + 1
        Next
        WB.Sheets("Download Sheet").Cells(2, 1).CopyFromRecordset RecordDownloadRs
        
        Set RecordDownloadRs = Nothing
       Else
             Msgbox "More than 1048570 records. The program does not download any records."
       End If

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:

Dim OutputCell as Range
set outputCell = WB.Sheets("Download Sheet").Cells(2, 1)
do until RecordDownloadRs.EOF
outputcell.CopyFromRecordset RecordDownloadRs, 1000000, 5
set outputcell = outputcell.offset(, 5)
loop

Originally Posted by rorya

You can just loop:

Dim OutputCell as Range
set outputCell = WB.Sheets("Download Sheet").Cells(2, 1)
do until RecordDownloadRs.EOF
outputcell.CopyFromRecordset RecordDownloadRs, 1000000, 5
set outputcell = outputcell.offset(, 5)
loop

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"

Sub Test()
'   Haluk - 24/03/2024
    
    Const adOpenKeyset = 1
    
    MaxRows = 18
    
    Set sh = Sheets("Sheet2")
    sh.Activate
    sh.Cells.Clear
    
    Set objConn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.RecordSet")
    
    strArgs = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Readonly=False; DBQ=" & ThisWorkbook.FullName
    objConn.Open strArgs
   
    InputGroupNumber = 108
    
    strSQL = "Select Group_number, ID, FirstName, LastName, Score From [Sheet1$] Where Group_number = " & InputGroupNumber
    
    Set RS = objConn.Execute(strSQL)
    RS.Close
    RS.CursorType = adOpenKeyset
    RS.Open

    iCount = RS.RecordCount
    c = 1
    
    Z = IIf(iCount Mod MaxRows = 0, iCount / MaxRows, Int(iCount / MaxRows) + 1)
    
    For i = 1 To Z
        Range(sh.Cells(1, c), sh.Cells(1, c + 4)) = Array("Group_number", "ID", "FirstName", "LastName", "Score")
        sh.Cells(2, c).CopyFromRecordset RS, MaxRows
        c = c + 5
    Next
    
    RS.Close
    objConn.Close
    
    Set RS = Nothing
    Set objConn = Nothing
End Sub