Solved

copying one recordset to another recordset through function

Posted on 2001-09-07
20
286 Views
Last Modified: 2013-11-25
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, _
         LockType:=adLockOptimistic
'''''''''''''''''''''''''''''''
'Error with the nextStatement
    getRecordSet = rst
'''''''''''''''''''''''''''''''
      .Close
   End With

   ' Close connection and destroy object variables.
   cnn.Close
   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"
0
Comment
Question by:etstiles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
  • +5
20 Comments
 
LVL 5

Expert Comment

by:rkot2000
ID: 6465047
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
    Else
      ljrsNew.Fields.Append ljField.Name, ljField.Type, ljField.DefinedSize
    End If
  Next
  ljrsNew.Open
 
  If lvfOnlyStructure = True Then GoTo Exit_
 
  If ljrsSource.RecordCount > 0 Then
    ljrsSource.MoveFirst
  End If
  For lvlCnt = 0 To ljrsSource.RecordCount - 1
    ljrsNew.AddNew
    For Each ljField In ljrsSource.Fields
      ljrsNew.Fields(ljField.Name).Value = ljrsSource.Fields(ljField.Name).Value
    Next
    ljrsNew.Update
    ljrsSource.MoveNext
  Next lvlCnt
 
Exit_:
  Set CloneRs = ljrsNew
End Function
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6465107
you need to use

'Error with the nextStatement
set   getRecordSet = rst
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6465119
for #2 did you check readonly flag?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6465225
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
at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaccgen/html/msdn_harness.asp

especially the last part titled
Multitable Query Updatability Restrictions

Anthony
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6465239
ado clone - doesn't copy a recordset it just creates another pointer to it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6465262
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.
Anthony
0
 
LVL 1

Expert Comment

by:alaplume
ID: 6465661
Reference the Microsoft scripting runtime

save the rs to a stream

create a new rs

restore the new rs from the stream
0
 
LVL 6

Expert Comment

by:JonFish85
ID: 6466020
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!
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6466315
Also check that all of the tables have a primary key.
0
 
LVL 17

Expert Comment

by:inthedark
ID: 6466346
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.

0
 
LVL 17

Accepted Solution

by:
inthedark earned 125 total points
ID: 6466369
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.
0
 

Author Comment

by:etstiles
ID: 6470436
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.
0
 

Author Comment

by:etstiles
ID: 6470437
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 6470744
>>> 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.
0
 

Author Comment

by:etstiles
ID: 6471271
I hope my points were recieved.  I'm not sure what happened.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7207738
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
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7209147
Definitely not to inthedark
His answer has too many wrong statements.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7210031
rkot2000,
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
0
 
LVL 5

Expert Comment

by:rkot2000
ID: 7210065
did n't see his reply :))
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7241056
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month3 days, 18 hours left to enroll

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question