Solved

copying one recordset to another recordset through function

Posted on 2001-09-07
20
284 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Determine Range to Select 5 64
Run code from text file in vb 1 103
MS Date Picker 64 bit 32 bit issue 12 73
Prevent user closing word document opened with VB6 6 89
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

738 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