CODEWITHSUNDEEP
excelvba
Rupesh Ghosh
Rupesh Ghosh

Reputation: 57

Runtime error '28': Out of stack space in Excel VBA and Excel crashes

I am able to run the code and build a connection but when I am trying to insert data into a MySQL Database from Excel using VBA. It shows '28: Out of Stack space'

I have a code and this is the data here

Name     Analyst     Method    Numsample   Condition
AAA       AAA        AAA         2           ABC

Below is the code I have written,


Dim oConn As ADODB.Connection
Private Sub ConnectDB()

   On Error GoTo ErrHandle

   Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=******************t-1.rds.amazonaws.com;" & _
        "DATABASE=worksheet;" & _
        "USER=***;" & _
        "PASSWORD=****;" & _
        "Option=3"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
    ConnectDB
    With wsBooks
        For rowCursor = 2 To 3
            strSQL = "INSERT INTO TestExperiment (Experiment_Name, Experiment_Method, Experiment_Analyst, Experiment_NumSample, Experiment_condition) " & _
                "VALUES ('" & esc(.Cells(rowCursor, 1)) & "', " & _
                "'" & esc(.Cells(rowCursor, 2)) & "', " & _
                "'" & esc(.Cells(rowCursor, 3)) & "', " & _
                "'" & esc(.Cells(rowCursor, 4)) & "', " & _
                esc(.Cells(rowCursor, 5)) & ")"
            rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
        Next
    End With
    MsgBox "Connection successful"

ExitHandle:
   Set con = Nothing  ' RELEASE ALL set OBJECTS
   Exit Sub

ErrHandle:
   MsgBox Err.Number & ": " & Err.Description
   Resume ExitHandle
    End Sub

Function esc(txt As String)
    esc = Trim(Replace(txt, "'", "\'"))
End Function

Completely new to VBA, just learning code might have some errors. Please help me if you can thanks.

Upvotes: 1

Views: 762

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

"Out of stack space" is VBA's StackOverflowException. It means you have inadvertently written recursive logic that never escapes the recursion, keeps digging, and digging deeper, ....until the call stack is completely filled and VBA can't track where to return anymore.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
    ConnectDB '<~ recursive call!
    With wsBooks

Remove the recursive call, problem solved!

Upvotes: 2

Related Questions