If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Data Access, Manipulation & Batch Languages > Visual Basic > Method 'Open' of object '_Recordset' failed

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
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...

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 07:57.
Reply With Quote
  #2 (permalink)  
Registered User
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.
Reply With Quote
  #3 (permalink)  
Registered User
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?
Reply With Quote
  #4 (permalink)  
Join Date: Jan 2007
Location: UK
Posts: 11,054
Post full code, highlighting the line causing the error message
Home | Blog
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On