3

Since I received so nice and fast solution to my problem, I will try again to get some help from you:

I opened two Recordsets.

 Set cmd1.ActiveConnection = cn1
 cmd1.CommandText = "SELECT * FROM mov Where [Date] >= #" & DateA & "#;"
 Set RSold = cmd1.Execute

 Set cmd2.ActiveConnection = cn2
 cmd2.CommandText = "SELECT * FROM mov"
 Set RSnew = cmd2.Execute

(I want to save only selected records of a file.)

I know how to copy record by record, but is there a 'Short Cut' to do it faster ?

Thanks

user1838163
  • 43
  • 1
  • 2
  • 5
  • 1
    possible duplicate of [**Deep Copy or Clone an ADODB recordset in VBA**](http://stackoverflow.com/questions/25553594/deep-copy-or-clone-an-adodb-recordset-in-vba) –  Aug 28 '14 at 17:25

4 Answers4

0

try this:

Dim i As Long
Do While Not RSold.EOF
    ' You can place if condition here
    RSNew.AddNew
    For i = 0 To RSold.Fields.Count - 1
        RSNew.Fields(RSold.Fields(i).Name) = RSold.Fields(i).Value
    Next i
    RSNew.Update
    RSold.MoveNext
Loop

This will copy records from RSold to RSnew recordset

hassan
  • 64
  • 5
  • One note: You may have to special-case any autonumber fields that you have so that they are skipped. Inside the For loop, add code that does a check for the field name, and skips setting the field. – Lynn Crumbling Nov 20 '12 at 16:14
  • Thanks Hassan. I am trying to do the copy as quick as possible. – user1838163 Nov 21 '12 at 06:30
  • @hassan : Above copy process making my new recodset status to close. – Preeti Nov 24 '12 at 05:18
0

I think this will do what you want by doing it all at once.

Dim objPB As New PropertyBag

objPB.WriteProperty "rs", RSOld
Set RSNew = objPB.ReadProperty("rs")

Set objPB = Nothing
Joe M
  • 2,142
  • 3
  • 28
  • 60
  • Thank you Joe. This is exactly what I am Looking for. When I run the code, I am getting an Error "No such interface supported. -2147467262. – user1838163 Nov 21 '12 at 06:29
  • Please assist. I am still stuck with this problem. Surched the net, can not find solution. – user1838163 Nov 22 '12 at 07:28
  • @Joe Majsterski : I want to update new recorset i.e RSNew.But above copy process not allowing me to update record set. – Preeti Nov 24 '12 at 05:15
0

You Can use code :

Set RSNew = RSOld.Clone

@user1838163 :Saving the second Recordset as a file

Dim RFileNm  As String 
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")

RFileNm  = "c:\temp\" & Trim(RFileNm) & ".adt"
fs.DeleteFile (RFileNm)
RSNew .Save RFileNm, adPersistADTG
RSNew .Close
RSNew .Open RFileNm, , , , adCmdFile
Preeti
  • 1,446
  • 8
  • 54
  • 112
  • Thanks Preeti. If I use the 'Clone', how do I save the second Recordset as a file ? – user1838163 Nov 27 '12 at 12:36
  • Thank you. When I run the CLONE line, I get the following Error message: "Current recordset does not support bookmarks. This may be a limitation of the provider or of the selected cursortype" What am I doing wrong ? – user1838163 Nov 28 '12 at 08:52
  • The recordset is probably a ForwardOnly, ReadOnly recordset. Make sure the table has a Primary Key. – Preeti Nov 28 '12 at 10:31
  • Ok.Good. Got that. Now It is runing, but the File created can not be open in ACCESS. The Error message is "Not recognized file format". Sorry... I appretiate your help. – user1838163 Nov 28 '12 at 11:33
  • create access file in place of ".adt" file – Preeti Nov 29 '12 at 05:21
  • If I create the Access File, then the Error say that the File is already exist. If I use the extention as ".MDB", then I can not open the File using Access. – user1838163 Nov 29 '12 at 10:43
0

I don't think CLONE is going to do what you want. It just gives you another view of the same recordset you already have. This allows you to use multiple bookmarks and so forth, but the recordset is still attached to the same database the original was. I also need a way to copy the recordset and save it to a new database in a new format.

John Rasor
  • 1
  • Hi, and welcome to SO. FYI, if you don't have the answer to the question, it's best to put your content in a comment rather than an answer. – DWright Jan 04 '13 at 23:20