Solved

copying one recordset to another recordset through function

Posted on 2001-09-07
20
277 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
  • 7
  • 3
  • 3
  • +5
20 Comments
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
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
Comment Utility
you need to use

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

Expert Comment

by:rkot2000
Comment Utility
for #2 did you check readonly flag?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
ado clone - doesn't copy a recordset it just creates another pointer to it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Also check that all of the tables have a primary key.
0
 
LVL 17

Expert Comment

by:inthedark
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 17

Accepted Solution

by:
inthedark earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
>>> 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
Comment Utility
I hope my points were recieved.  I'm not sure what happened.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
Comment Utility
Definitely not to inthedark
His answer has too many wrong statements.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
Comment Utility
did n't see his reply :))
0
 
LVL 5

Expert Comment

by:Netminder
Comment Utility
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now