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

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

    Code:
    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, _
            LockType:=adLockReadOnly
    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, _
                LockType:=adLockReadOnly
        End With
        
        lngRow = 2
        
        rstV.MoveFirst
        
        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
            rstV.MoveNext
        Loop
               
        rstV.Close
           
        lngCol = lngCol + 2
        
    Next
    
    Set wksF = Nothing
    cnnDB.Close
    Set cnnDB = Nothing
    Set rstRS = Nothing
    Set rstV = Nothing
    
    End Sub
    Last edited by rich8008; 03-14-06 at 07:57.

  2. #2
    Join Date
    Nov 2006
    Posts
    1

    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
    Posts
    1
    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
    Location
    UK
    Posts
    11,090
    Post full code, highlighting the line causing the error message
    George
    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
  •