Error with MySQL query string longer than 255 characters

[JPLouw]

Hi,

I keep getting a Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. I am using Excel 2010. I am running a MySQL query through VBA that is longer than 255 characters, to get around it I am trying to input it as an array. I have read the previous blogs, but I am still stuck. Can you please help, I have attached the VBA coding below.

Thanks in advance,



Sub MySQL1()

'
' MySQL Macro - code sourced from MrExcel
'


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 Variant
Dim Rs As ADODB.Recordset


sqlstr = "SELECT mfh.symbol, mfh.date, mfh.close, dch.div_amount, dch.cap_gain_amount, sh.num_new, sh.num_old, mfh.volume" _
& "FROM `datafeed`.csi_stock_etf_historical mfh left outer join `datafeed`.csi_div_cap_historical dch" _
& "on (mfh.csi_num = dch.csi_num and mfh.date = dch.date)" _
& "left outer join `datafeed`.csi_split_historical sh" _
& "on (mfh.csi_num = sh.csi_num and mfh.date = sh.date) where mfh.symbol = 'anv' order by mfh.date desc limit 3000"


Server_Name = "XXXXXX"
Database_Name = "XXXX"
User_ID = "XXXX"
Password = "XXXXX"


Set Cn = New ADODB.Connection
Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";OPTION=16427"



Set Rs = New ADODB.Recordset
Rs.Open StringToArray(sqlstring), Cn, adOpenStatic[/COLOR]
With ThisWorkbook.Worksheets("CSI").Range("A:D")
.ClearContents
.CopyFromRecordset Rs
End With



End Sub


'--------------------------------------------------------

Function StringToArray(sqlstring As Variant) As Variant

Const StrLen = 250

Dim NumElems As Integer
Dim Temp() As String

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

StringToArray = Temp

End Function

 

[Ruddles]

I've seen it but I've never worked out why you'd do it that way. I guess it's one of those things that are there if you know about them and need them, but which you can safely ignore otherwise.

 

[Norie]

I can perhaps see some use to storing the SQL in a array

Then using the array, or parts of it, to construct the SQL.

Of course when you record a macro in Excel for importing data the generated code uses an array.