Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006

    Unanswered: Method 'Open' of object '_Recordset' failed

    Updated 3/14/06:

    I think that the problem may be that I am re-querying the same table more then one time. I am running on query to essentially get a field list and while I go through each field I am re-querying for all of the values in the field. Can this be done?

    Is there some rules or limit to how many times I can re-query the same table? Any help anyone has would be greatly appreciated.

    End of Update.

    I am getting the following error in Visual Basic:

    Run-time error '-2147467259 (80004005)':
    Method 'Open' of object '_Recordset' failed

    What I have done is:
    Open a Connection to an Access Database
    Open a Recordset of the whole table
    For each field in the Table I am opening another record set pulling the distinct values and counting them

    It has worked on the first 2 fields but then gives the error above on the 3rd.

    The code is below if it helps...

    Option Explicit
    Public Const DB_PATH As String = "C:\My Documents\db1.mdb"
    Public Const DB_PROVIDER As String = "Microsoft.Jet.OLEDB.4.0"
    Sub Pull_Attributes()
    Dim cnnDB   As ADODB.Connection
    Dim rstRS   As ADODB.Recordset
    Dim rstV    As ADODB.Recordset
    Dim fld     As ADODB.Field
    Dim wksF    As Excel.Worksheet
    Dim strS    As String
    Dim strF    As String
    Dim lngRow  As Long
    Dim lngCol  As Long
    Set wksF = Worksheets("Fields")
    Set cnnDB = New ADODB.Connection
    ' Open Connection
    With cnnDB
        .Provider = DB_PROVIDER
        .Open DB_PATH
    End With
    Set rstRS = New ADODB.Recordset
    Set rstV = New ADODB.Recordset
    With rstRS
        ' Opens the Recordset with the options below
        .Open _
            Source:="SELECT * FROM testtrg", _
            ActiveConnection:=cnnDB, _
            CursorType:=adOpenForwardOnly, _
    End With
    lngCol = 1
    For Each fld In rstRS.Fields
        strF = fld.Name
        wksF.Cells(1, lngCol).Value = fld.Name
        strS = "SELECT testtrg." & strF & ", Count(testtrg." & strF & ") As [Count] " & _
                            "FROM testtrg " & _
                            "GROUP BY testtrg." & strF & " " & _
                            "ORDER BY Count(testtrg." & strF & ") DESC"
        With rstV
            .Open _
                Source:=strS, _
                ActiveConnection:=cnnDB, _
                CursorType:=adOpenForwardOnly, _
        End With
        lngRow = 2
        Do Until rstV.EOF Or lngRow > 10000
            wksF.Cells(lngRow, lngCol).Value = rstV(strF)
            wksF.Cells(lngRow, lngCol + 1).Value = rstV("Count")
            lngRow = lngRow + 1
        lngCol = lngCol + 2
    Set wksF = Nothing
    Set cnnDB = Nothing
    Set rstRS = Nothing
    Set rstV = Nothing
    End Sub
    Last edited by rich8008; 03-14-06 at 08:57.

  2. #2
    Join Date
    Nov 2006

    Try this

    Hi, bracket your field names or in your case, bracket before and after your variable in your building your sql clause. One of your fields must be a ANSI SQL reserved word.

  3. #3
    Join Date
    Oct 2008
    Hi All,

    If I'm not mentioning the field names instead I use * in the select statement also I'm getting the same error.

    My code part is given below.

    //Dim recRasi As ADODB.Recordset
    strsql = "Select * from rasi Order By Position"
    If recRasi Is Nothing Then Set recRasi = New ADODB.Recordset
    recRasi.Open strsql, connDB, adOpenStatic, adLockReadOnly

    And I tried with square Brackets with each fields also.

    Can anyone help me on this?

  4. #4
    Join Date
    Jan 2007
    Provided Answers: 12
    Post full code, highlighting the line causing the error message
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts