copying one recordset to another recordset through function

For quality answers I will gladly raise up to another 50 points.

1.I have provided more information than is probably necessary, but I cannot figure out the solution.  How do I copy a recordset to another recordset.  The following code fails at the line getRecordSet = rst?  

Public Function getRecordSet(strDBPath As String, strSQL As String) As ADODB.Recordset
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset

   Set cnn = New ADODB.Connection
   ' Open the connection.
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   Set rst = New ADODB.Recordset
   With rst
      ' Open the Recordset object.
      .Open Source:=strSQL, _
         ActiveConnection:=cnn, _
         CursorType:=adOpenKeyset, _
'Error with the nextStatement
    getRecordSet = rst
   End With

   ' Close connection and destroy object variables.
   Set rst = Nothing
   Set cnn = Nothing
End Function

2.I have a data object with a record source that JOINs several tables together(Look below for sql).  Can the fields in the data.recordset be edited. If so how (the data.recordset.edit command give me an error telling me that the database or object is read only)?  The record source with information from one table can be edited.

Const sqlMain As String = "SELECT Table_IssuesResolution.Issue, Table_IssuesResolution.Resolution, " & _
    "TableFiles.Files, Table_Owner.Owner,Table_IssuesResolution.Owner, Table_Category.CategoryType, Table_Status.Status, " & _
    "Table_IssuesResolution.ID, Table_IssuesResolution.Category  FROM Table_Status RIGHT JOIN ((Table_Owner RIGHT JOIN " & _
    "(TableFiles RIGHT JOIN Table_IssuesResolution ON TableFiles.FileID = " & _
    "Table_IssuesResolution.ID) ON Table_Owner.ID = Table_IssuesResolution.Owner) LEFT JOIN " & _
    "Table_Category ON Table_IssuesResolution.Category = Table_Category.ID) ON " & _
    "Table_Status.Status_ID = Table_IssuesResolution.Status"
Who is Participating?
From you code I see you are trying to do something impossible. You are trying to Open a recordset and return it as the result of a function.  But when you close your connection and recordset object the recodeset will nolonger exist and you wont be able to use it.

You have to use a stratagy like rkot2000.  But using this stratagy will not allow changes to the data.

You have to either keep the recordset open or use a different method for holding and saving the data.

For example change your function data type:

Public Function getRecordSet(strDBPath As String, strSQL As String) As Variant

and instead of

'Error with the nextStatement
   getRecordSet = rst

Change to:

getRecordSet = rst.GetRows

You could now use an update query to change any of the values in the recordset.

But it would be simpler to keep your code as is and just delete the bit that closes the connection and recordset.

And use the Set getRecordset = rst as suggested by acperkins and rkot2000.

It would also make more sense to pass the connection object to your function as you would want to be able to close the recordset and connection and set them to nothing at a later date.
i am using this to copy rs:

Public Function CloneRs(ByRef ljrsSource As ADOR.Recordset, _
                        Optional ByVal lvfOnlyStructure As Boolean = False) As ADOR.Recordset
  Dim ljrsNew As ADOR.Recordset
  Dim lvlCnt As Long
  Dim ljField As ADOR.Field
  Dim lvlAttributes As Long
  Set ljrsNew = New ADOR.Recordset
  ljrsNew.CursorType = adOpenStatic
  ljrsNew.CursorLocation = adUseClient
  For Each ljField In ljrsSource.Fields
    lvlAttributes = ljField.Attributes
    If lvlAttributes And adFldIsNullable Then
      ljrsNew.Fields.Append ljField.Name, ljField.Type, ljField.DefinedSize, adFldIsNullable
      ljrsNew.Fields.Append ljField.Name, ljField.Type, ljField.DefinedSize
    End If
  If lvfOnlyStructure = True Then GoTo Exit_
  If ljrsSource.RecordCount > 0 Then
  End If
  For lvlCnt = 0 To ljrsSource.RecordCount - 1
    For Each ljField In ljrsSource.Fields
      ljrsNew.Fields(ljField.Name).Value = ljrsSource.Fields(ljField.Name).Value
  Next lvlCnt
  Set CloneRs = ljrsNew
End Function
you need to use

'Error with the nextStatement
set   getRecordSet = rst
Ultimate Tool Kit for Technology Solution Provider

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

for #2 did you check readonly flag?
Anthony PerkinsCommented:
1.  As rkot2000 has pointed out the correct syntax is:
Set getRecordset = rst
However, you may not be aware that you are not copying a recordset, but rather instantiating an object and pointing at the original recordset.  In order to copy the recordset you would have to do something like rkot2000 suggested.  In addition you may want to take a look at the Clone method.

2. Take a look at the article on MSDN titled:
Harnessing the Power of Updatable Queries

especially the last part titled
Multitable Query Updatability Restrictions

ado clone - doesn't copy a recordset it just creates another pointer to it.
Anthony PerkinsCommented:
Thanks rkot2000, I realize that. I pointed out in my comment that if they wanted a copy of the recordset than something like your code should be used. Otherwise if this is not what they wanted than there code could be achieved a lot easier by using the Clone method.

Sorry I was not clear the first time.
Reference the Microsoft scripting runtime

save the rs to a stream

create a new rs

restore the new rs from the stream
try this maybe:

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

and then:

CopyMemory Recordset2, Recordset1, Len(Recordset1)

hope this helps!
Also check that all of the tables have a primary key.
Why do you want to copy the recordset?

If you want to copy the table you can do with with SQL.

If you want to keed a record of what the data was in the recordset you can use the clone methos or a simple GetRows Method.

Redim HoldRSData() as Variant
Dim RS As ADODB.Recordset

' Open your data

' and to take a copy
HoldRSData = RS.GetRows

' And to look at the data say row 3, field 5:

MsgBox Format(HoldRSData(3,5))

Hope this helps.

etstilesAuthor Commented:
Everyone gave me part of what I was looking for.  I figured out the 'set rs' error about 20 minutes after I asked the question, but inthedark provided the most complete picture to my problem.  Basically, I can't do what I want, how I want to do it.

I would spread the points around if I could.

Thank you everyone for the help.
etstilesAuthor Commented:
>>> But when you close your connection and recordset object the
recodeset will nolonger exist and you wont be able to use it.

It?s not correct you can use a disconnected recordset.

>>>But using this stratagy will not allow changes to the data.

I am calling sp to update data db and update local cached data.

I have an application with > 100 users so I can?t afford to keep all connections open all use rs objects to update the database.

Plus I am using middle tier to access data.
etstilesAuthor Commented:
I hope my points were recieved.  I'm not sure what happened.
Hi etstiles,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept inthedark's comment(s) as an answer.

etstiles, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer
Definitely not to inthedark
His answer has too many wrong statements.
The Asker, etstiles said:

>>...but inthedark provided the most complete picture to my problem

And the Customer is always right, don't you agree?.

-- Dan
did n't see his reply :))
Per recommendation, force-accepted.

CS Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.