Recordset clone error 13

Dim rs As Recordset
Set rs = Me.RecordsetClone

I can not get the above syntax to work with Access 2000. I get an error 13
I tried rs as dbodb.recordset same result. What is is the correct syntax?
davidpmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brewdogCommented:
From what I've heard (unfortunately I don't have A2K yet), it sounds like you need to specify what kind of recordset you want, like so:

Dim rs as ADO.recordset (or DAO.recordset)
set rs = me.recordsetclone

does that work?
0
TrygveCommented:
If you use ADODB, which is part of the the "new world", this is a typical setting for a find something code (in this case, refresh and return to current record):


On Error GoTo ErrorHandling

    Dim MyRecs As New ADODB.Recordset
    Dim myBookmark, CurrentID

    CurrentID = me!cID
    Me.Refresh
    DoCmd.GoToRecord , , acLast
    Set MyRecs = Me.RecordsetClone
    MyRecs.Find "ID='" & CurrentID & "'", , adSearchForward, 1
    myBookmark = MyRecs.Bookmark
    Me.Bookmark = myBookmark

ExitMe:
    MyRecs.Close
    Set MyRecs = Nothing
    Exit Function
   
ErrorHandling:
    Select Case Err
        Case 3021   'Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.
            GoTo ExitMe
        Case Else
            msgbox(err & " " & error)
    End Select
    GoTo ExitMe
0
davidpmAuthor Commented:
Yes thats what I thought should work but the following two lines fail

Dim rs As New ADODB.Recordset
Set rs = Me.RecordsetClone

If I leave out the new keyword it fails also.

It's as if the RecordsetClone is returning some different type of recordset.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

davidpmAuthor Commented:
I also tried

Dim rs As ado.Recordset
Set rs = Me.RecordsetClone


And also
Dim rs As dao.Recordset
Set rs = Me.RecordsetClone

all to no avail

Perhaps there is some environmental thing that needs to be set.

This is just a default install of access 2000 on win 98
0
brewdogCommented:
if you go to Debug | Compile Loaded Modules, does it highlight anything? I think Trygve's syntax is more accurate, with the daodb.recordset or adodb.recordset. If Access chokes when you try to compile those, then I'd go to Tools | References and make sure you have references to ADO and DAO defined in there.
0
davidpmAuthor Commented:
The following code works. It's not as if adodb is not connected.

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst As New ADODB.Recordset
rst.Open "PoNumber", cnn

The following line does not give an error
dim rs as new ADODB.Recordset
This is the line that errors out.
set rs = me.recordsetclone

What references should be in tools, referances.
I have VBA, 9.0 object library, OLE animation and Active x Dataobjects 2.1

Do the following lines execute properly on your sytem in a doubleclick event for example.

dim rs as new ADODB.Recordset
set rs = me.recordsetclone
0
brewdogCommented:
I just checked a couple things, both pretty basic. First, I compiled the code -- that was fine. (Though I don't have ADO, since I'm working in 97; I just declared recordset and it worked.) Second, I played with the recordsource for the form. If that is blank, I get an error, too. Is your form a bound form?
0
davidpmAuthor Commented:
I really appreciate your efforts but I too have no problem with access 97. I believe this is a access 2000 specific issue. Do you have access to access 2000?

Yes the form is bound. I bet you do not get error 13 though.
0
davidpmAuthor Commented:
The following code works also so I know the form is OK
    Me.RecordsetClone.findfirst "[PONumber] = " & Me![cboLookup]
  Me.Bookmark = Me.RecordsetClone.Bookmark

0
blakeh1Commented:
You may want to make a reference to the DAO object library, then use the following, after adding this ref using the below code will run without runtime err 13

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
MsgBox rs.RecordCount
Set rs = Nothing
0
davidpmAuthor Commented:
blakeh1 your code works
I had to make a referance to DAO 3.6 object library.

Does anyone know what the ramificatations of this are.
Is DAO 3.6 concidered current code if so why is it not loaded automatically like adodb.
If it is not is there another way to use recordsetclone that does not require dao 3.6

I want to not use legacy stuff if not necessary.

0
brewdogCommented:
DAO 3.6 is current, but it has limitations for data sources. ADO is Microsoft's new standard because it can use just about anything as a data source. I didn't realize the RecordSetClone was not an ADO method . . . that's both good to know and bad to hear.

Microsoft probably only loads one because if you declare a "recordset" without the ADODB or DAODB object, you can't guarantee which one you'll get. And, as you've seen, they have different methods and all that. If you do leave it loaded (I don't know another way to use RecordsetClone), make sure that your object variables are set to ADO-type objects.
0
davidpmAuthor Commented:
Brewdog
Can you confirm on your system that recordsetclone does not work with adodb. It would seem very odd that a built in function like recordsetclone would be broke out of the box like that.
0
brewdogCommented:
like I said, I don't have Access 2000 on a machine here, so unfortunately I can't help you out with that one.
0
blakeh1Commented:
Actually it seems that to refer to the recordset clone in ado you would actually do

Dim frm As Form
Set frm = Me
With frm.RecordsetClone
    MsgBox .RecordCount
End With
Set frm = Nothing

another way of not using dao is to dim rs as an object instead and let access assign it for you

Dim rs As Object
Set rs = Me.RecordsetClone
MsgBox rs.RecordCount
Set rs = Nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
blakeh1Commented:
actually, just to clarify, the above 2 do not need a reference to the Dao library.
0
blakeh1Commented:
Did some more checking. Found that recordsetclone is not a method or property of adodb. If you use the object browser and search this library only, it will not be found. It is a property of access.form in the Access library. According to help they show an example of
dim rst as recordset
set rst = me.recordsetclone but do not specifically say you need a reference to the dao obj lib
0
davidpmAuthor Commented:
Blake your dim rs as object does work for me also. It is all very odd. I will try it at my production machine just to make sure but it looks good.
I find the new Access docs very confusing regarding what syntex works with what environment.
0
TrygveCommented:
My code works, but it is based on the form having a SQL Server 7.0 table as its source. I have tested a bit using "normal" tables and here is code that can be used on both (Just change the lines that are commented)

Normal tables: Microsoft DAO 3.6 Object Library needs to be referenced
SQL Server Tables: Microsoft ActiveX Data Objects 2.1 Library needs to be references.

Private Sub Refresh_Click()

On Error GoTo ErrorHandling

    'Dim MyRecs As New ADODB.Recordset ' SQL Server tables
    Dim MyRecs As DAO.Recordset ' "Normal" MDB tables
    Dim myBookmark, CurrentID

    CurrentID = Me!PostCode
    Me.Refresh
    DoCmd.GoToRecord , , acLast ' To be sure that the bookmarks will work
    Set MyRecs = Me.RecordsetClone
   
    'MyRecs.Find "PostCode='" & CurrentID & "'", , adSearchForward, 1 ' SQL Server tables
    MyRecs.FindFirst "PostCode='" & CurrentID & "'" ' "Normal" MDB tables
   
    myBookmark = MyRecs.Bookmark
    Me.Bookmark = myBookmark

ExitMe:
    MyRecs.Close
    Set MyRecs = Nothing
    Exit Sub
     
ErrorHandling:
    Select Case Err
        Case 3021   'Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record.
            GoTo ExitMe
        Case Else
            MsgBox (Err & " " & Error)
    End Select
    GoTo ExitMe

End Sub

If you can't get it to work, here is some ideas:

- Is your data located in a subform?
- When do you run this code (from where)
- Send the form etc to me and I will have a closer look at it for you and post any findings here. EMail address is in my profile.
0
davidpmAuthor Commented:
You guys have been great. I've accepted the code that exactly answers my question.
dim rs as object
does not require referancing DAO 3.6
If as object works there must be another more specific syntax that works also but have not found it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.