Overflow when looping through SQL-inserts for an ADODB.connection

Question

I am trying to insert real-time data from a financial service provider into Excel, which can only be fetched with an Excel-plugin in blocks of roughly 100,000 values.

The code seems to be working as expected:

Sub insert()

'Declare Variables
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

Dim ColumnsSchema As ADODB.Recordset

Dim rsT As Variant

Dim i As Integer

For i = 0 To 2

    rsT = Join(Application.Transpose(ThisWorkbook.Sheets("Prices").Range(Cells(3 + i * 10000, 9), Cells(10002 + i * 10000, 9)).Value), " ")

    ' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=DB1;" 'rest of the string blackened

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    'Open the connection and execute.
    conn.Open sConnString

    Set rs = conn.Execute(rsT)

Next i

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

If I increase the counter i to 3, thus inserting more than 30000 with the for-loop, it results in an overflow-error.

I tried to split it into smaller blocks and a counter going to 49: Similar error.

The referenced cells in the sheet 'prices' seem correct. That's why I am only posting the VBA code here. As I am not really familiar with VBA and the limitations of the used objects, I would expect the issue there.

Answer 1

You can run multiple queries without creating a new connection each time.

Option Explicit

Sub insert()

    Const BATCH_SIZE = 10000 ' 10,000
    
    'Declare Variables
    Dim conn As ADODB.Connection, sConnString As String, SQL As String
    Dim ws As Worksheet, r As Long, LastRow As Long, n As Long
    Dim t0 As Single: t0 = Timer
       
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=DB1;" 'rest of the string blackened
    
    ' Create the Connection
    Set conn = New ADODB.Connection
    conn.Open sConnString
    
    Set ws = ThisWorkbook.Sheets("Prices")
    With ws
         LastRow = .Cells(Rows.Count, 9).End(xlUp).Row ' col I
         For r = 3 To LastRow Step BATCH_SIZE
             SQL = Join(Application.Transpose(ws.Cells(r, 9).Resize(BATCH_SIZE).Value), " ")
             conn.Execute SQL
             n = n + 1
         Next
    End With
      
    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

    MsgBox LastRow - 2 & " rows in " & n & " batches of max size " & BATCH_SIZE, _
           vbInformation, Format(t0 - Timer, "0.0 secs")
    
End Sub