[RESOLVED] Run-time error '-2147467249 (80004005)' Method 'Open' of object _Recordset' failed

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!!!!

test is dimensioned as a string, should be a database connection

I tried that, that made no difference :-(

However I re-wrote a separate function for opening the recordset:

Public Function open_recordset(SQL_String As String, Database_Connection As ADODB.Connection, Query_Start As String) As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open SQL_String, Database_Connection

Set open_recordset = rs

Field_Count = rs.Fields.Count
My_Array = rs.GetRows()

kolumner = UBound(My_Array, 1)
rader = UBound(My_Array, 2)

For k = 0 To kolumner ' Using For loop data are displayed

Range(Query_Start).Offset(0, k).Value = rs.Fields(k).Name
For R = 0 To rader
Range(Query_Start).Offset(R + 1, k).Value = My_Array(k, R)
Next
Next

rs.Close
Set rs = Nothing

End Function

and it seems to work.

I think possibly the problem was to do with variables, I also added:

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Anyway, so it works, but I'd be interested to know, if my understanding of why it works is correct, if anyone has a chance to look over my code!

actually test being an object variable, would require the set keyword to work correctly, but an error should have been raised

I changed the code around quite a bit in between events, so I suspect that did occur. Either way, I'll mark this as resolved now :-)