[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!!!!
Re: Run-time error '-2147467249 (80004005)' Method 'Open' of object _Recordset' faile
test is dimensioned as a string, should be a database connection
Re: Run-time error '-2147467249 (80004005)' Method 'Open' of object _Recordset' faile
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!
Re: Run-time error '-2147467249 (80004005)' Method 'Open' of object _Recordset' faile
actually test being an object variable, would require the set keyword to work correctly, but an error should have been raised
Re: Run-time error '-2147467249 (80004005)' Method 'Open' of object _Recordset' faile
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 :-)