I know there are hundreds of examples around, for some reason none work for me...

I have a connection to a MYSQL database, which worked fine, but when trying to tidy up the code it stopped working.

So I have this function:

Public Function create_connection(Server_Name, Database_Name, User_ID, Password) As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Driver={MySQL ODBC 5.2a Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

Set create_connection = cn
End Function

Then on a button click I have the following:

Dim SQLStr As String
Dim myArray()
Dim rs As ADODB.Recordset
Dim test As String

Set rs = New ADODB.Recordset
SQLStr = "select [currency_id] from [tblcurrency];"
test = create_connection(Server_Name, Database_Name, User_ID, Password)

rs.Open SQLStr, test, adOpenStatic

This just comes up with the error above, however if I take the code out of the function and just have it all under the button click, it works fine.

Can anyone help, this is so frustrating!!!!