?
Solved

copying one recordset to another recordset through function

Posted on 2001-09-07
20
Medium Priority
?
289 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 500 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

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.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

718 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