Site icon Casino Publishers

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

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.

Exit mobile version