dcsimg
Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,238

    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

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    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!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,238

    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

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    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
  •  



Featured


Click Here to Expand Forum to Full Width