-
Feb 16th, 2013, 10:46 AM
#1
Thread Starter
Lively Member
[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!!!!
-
Feb 16th, 2013, 03:47 PM
#2
Re: Run-time error '-2147467249 (80004005)' Method 'Open' of object _Recordset' faile
test is dimensioned as a string, should be a database connection
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 17th, 2013, 06:02 AM
#3
Thread Starter
Lively Member
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!
-
Feb 17th, 2013, 03:23 PM
#4
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 17th, 2013, 03:46 PM
#5
Thread Starter
Lively Member
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 :-)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|