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 connect

I 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.