mySQL connect function

[austin350s10]

I am looking for a way to connect to mySQL with a function so it can be reused passing different credentials each time it is called. Only thing is that I can't seem to use the connection after I run the SQLconnect function. I'm sure it's something easy but I don't know what to do. Can anyone help??


This is my attempt, but keep getting the error "object required"

Sub testUpdate()
Dim sqlstr As String

SQL = SQLconnect("127.0.0.1", "test", "root", "xxxxxxxx")

sqlstr = "UPDATE " & table1 & " SET " & field2 & " = 'thisGuy' WHERE " & field1 & " = '1'"

SQL.Execute sqlstr

End Sub

Public Function SQLconnect(server_name As String, database_name As String, user_id As String, password As String)

Dim conn As New ADODB.Connection

' Establish connection to the database
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=16427"

End Function

 

[Norie]

You aren't returning anything from the function, you should return the connection.

Something like this perhaps, though I haven't had a chance to check it.

Sub testUpdate()
Dim sqlstr As String
    Set Sql = SQLconnect("127.0.0.1", "test", "root", "xxxxxxxx")
    sqlstr = "UPDATE " & table1 & " SET " & field2 & " = 'thisGuy' WHERE " & field1 & " = '1'"
    Sql.Execute sqlstr
End Sub
 
Public Function SQLconnect(server_name As String, database_name As String, user_id As String, password As String) As ADODB.Connection
Dim conn As New ADODB.Connection
' Establish connection to the database
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=" & server_name _
& ";DATABASE=" & database_name _
& ";UID=" & user_id _
& ";PWD=" & password _
& ";OPTION=16427"
Set SQLconnect = conn
End Function

 

[austin350s10]

you rock!!! THANKS