Danroyd
08-14-2017, 05:18 AM
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
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