VBA function to return MySQL Data

[Danroyd]

Hello,

This is my first post here so hoping you can help.

I need a function to return a dataset to a Sub. What am I doing wrong?



Sub data()
Dim SQLstr As String
Dim rst As String
Dim ir As Integer
Dim ic As Integer






SQLstr = "SELECT * FROM machines;"
rst = connect(SQLstr)


ir = 1

While Not rst.EOF
For iCols = 0 To rst.Fields.Count - 1
Worksheets("Sheet1").Cells(ir, ic + 1).Value = rst.Fields(ic).Value
Next
rst.MoveNext
ir = ir + 1
Wend




End Sub




Public Function connect(SQLstr As String)
Dim Password As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String



Set rst = CreateObject("ADODB.Recordset")
Server_Name = "" 'IP ADDRESS HIDDEN
Database_Name = "" 'DATABASE HIDDEN
User_ID = "" ' USER HIDDEN
Password = "" ' PASSWORD HIDDEN


Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rst.Open SQLstr, cn, adopenstatic



Return connect
rst.Close
Set rst = Nothing
cn.Close

End Function

[mancubus]

how about trying mysql's excel add-in?
https://dev.mysql.com/downloads/windows/excel/

[Bob Phillips]

You close the recordset in the function, so you cannot access it outside. Why not just drop it into an array and return that array, something like



Sub data()
Dim SQLstr As String
Dim rst As Variant '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim ir As Integer
Dim ic As Integer

SQLstr = "SELECT * FROM machines;"
rst = GetData(SQLstr)

ir = 1

For ir = LBound(rst, 1) To UBound(rst, 1)

For ic = LBound(rst, 2) To UBound(rst, 2)

Worksheets("Sheet1").Cells(ir, ic + 1).Value = rst(ir, ic)
Next ic
Next ir
End Sub

Public Function GetData(SQLstr As String) As Variant '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim Password As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String

Set rst = CreateObject("ADODB.Recordset")
Server_Name = "" 'IP ADDRESS HIDDEN
Database_Name = "" 'DATABASE HIDDEN
User_ID = "" ' USER HIDDEN
Password = "" ' PASSWORD HIDDEN

Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rst.Open SQLstr, cn, adopenstatic
GetData = rst.GetRows '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
rst.Close
Set rst = Nothing
cn.Close

End Function

[p45cal]

Also I don't think vba supports
Return connectI think it's more likely to be
connect = something-or-other

[Bob Phillips]

I should also have mentioned that you can dump that array into the worksheet range directly, no looping, or even use the CopyFromRecordset method to drop the recordset into a range.

[Danroyd]

You close the recordset in the function, so you cannot access it outside. Why not just drop it into an array and return that array, something like



Sub data()
Dim SQLstr As String
Dim rst As Variant '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim ir As Integer
Dim ic As Integer

SQLstr = "SELECT * FROM machines;"
rst = GetData(SQLstr)

ir = 1

For ir = LBound(rst, 1) To UBound(rst, 1)

For ic = LBound(rst, 2) To UBound(rst, 2)

Worksheets("Sheet1").Cells(ir, ic + 1).Value = rst(ir, ic)
Next ic
Next ir
End Sub

Public Function GetData(SQLstr As String) As Variant '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Dim Password As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String

Set rst = CreateObject("ADODB.Recordset")
Server_Name = "" 'IP ADDRESS HIDDEN
Database_Name = "" 'DATABASE HIDDEN
User_ID = "" ' USER HIDDEN
Password = "" ' PASSWORD HIDDEN

Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=" & Server_Name & ";Database=" & Database_Name & ";Uid=" & User_ID & ";Pwd=" & Password & ";"

rst.Open SQLstr, cn, adopenstatic
GetData = rst.GetRows '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
rst.Close
Set rst = Nothing
cn.Close

End Function

Thank you very much that will work perfectly.