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

I am trying to figure out a quick, automatized way to insert some real-time date from a financial service provider into excel, which can only be fetched with an excel-plugin in blocks of roughly 100,000 values. At the moment I am using the code below and so far it 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

My current problem is as soon as I increase the counter i to 3, thus inserting more than 30000 with the for-loop, it results in an overflow-error. I already tried to split it up in smaller blocks and a counter going to 49: Similar error.

The referenced cells in the sheet 'prices' seem all 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 and maybe someone with more VBA-experience here can see it at a glance.

Any advice is highly appreciated and thank you very much for your time to read this post. Hope I could at least make my case clear enough.

CodePudding user response：

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