Error in VBA: Lost Connection to MySQL Server at 'reading authorization packet',system error 2

This is the error which I get after my macro executes for about 10 mins. The macro is basically performing the task to update the MySQL database every five seconds.

What could be wrong?

My macro is as below:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 ' five seconds
Public Const cRunWhat = "UpdateMarketData"  ' the name of the procedure to run

Sub UpdateMarketData()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set开发者_Python百科 rs = New ADODB.Recordset

    rad = 0



  TextStrang = TextStrang & "'"
  field2 = "cid"
  field1 = "bid"
  table1 = "MMbanner"

  For rowCursor = 3 To 4

    SQLStr = "UPDATE tbl_MarketData SET Mid = '" & Cells(rowCursor, 2) & "',Bid = '" & Cells(rowCursor, 5) & "',Offer = '" & Cells(rowCursor, 6) & "',DateEntered = '" & Format(DateTime.Now(), "yyyy-MM-dd hh:mm:ss") & "' WHERE IndexCode = '" & Cells(rowCursor, 1) & "'"
    Debug.Print SQLStr
    Set Cn = New ADODB.Connection

    Cn.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=<Servername>; PORT=Portno; DATABASE=Databasename; USER=Username; PASSWORD=Password; OPTION=0;"

    Cn.Execute SQLStr
    rad = rad + 1
  Next
Set rs = Nothing
Cn.Close
Set Cn = Nothing

Call StartTimer

End Sub


Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub

---

Your for loop creates two separate connections via Set Cn = New ADODB.Connection etc but Cn.Close is only called once after the for loop. Therefore, the first connection created isn't explicitly closed and may still be considered active by the server. Eventually you'll run out of available connections and things will grind to a halt.

Not sure if this is the cause of your problem but it's definitely worth fixing. I would create and open the connection prior to the for loop but keep the Cn.Execute part within the body of the loop