Copy whole record from one recordset to another (ADO) 1

[CraigBest]

I'm trying to inplement a new feature in a program I wrote a couple of years ago, the users would like to take a completed, existing record and re-create it as a new separate record, identical except for the control # (Which is also the key field for the record).

If I recall correctly, you can't simply change the key field name on the record and save it, right? Wouldn't the original record be lost? I don't want this to happen.

What I have in mind is to create two recordsets. Use the first to read the completed record. Open the second and give it an .AddNew command. Copy the record from one RS to the other, change the Control # on the second RS and save the record.

Two questions:

First, is there a command in ADO that lets you copy the data from one identical recordset to another?

Second, both RS' would be attached to the same table. Would that be a problem?

If there's a better way to do this please feel free to let me know. Thanks. I'm searching for an answer in my books but haven't found the answer yet.

 

[simonkue]

Craig,
I used to do this in Paradox with the command COPYFROMARRAY and COPYTOARRAY. Not much use to you, but an indication that time doesn't always provide progress!
In similar situations in VB, I use a simple loop using the Fields collection. In this way, I loop through all the fields in recordset 1 and match to the same field in recordset 2. On a match, then I copy the data. Incidentally, I always search through all fields in recordset 2 in case the fields aren't in the same order!
eg

rsQBaseRejects.AddNew
For xnIndex = 0 To rsQBaseOPHIn.Fields.Count - 1
For xnIndex2 = 0 To rsQBaseRejects.Fields.Count - 1
If rsQBaseOPHIn.Fields(xnIndex).Name = rsQBaseRejects.Fields(xnIndex2).Name Then
rsQBaseRejects.Fields(xnIndex2).Value = rsQBaseOPHIn.Fields(xnIndex).Value
End If
Next
Next
rsQBaseRejects.Update

HTH

 

[JCruz063]

CraigHartz,
I'm not sure if there is a method that allows you to copy a record from one recordset to another, but I don't think there is.

What I would do is just create one recordset to retrieve the record in question. Then, change whatever you want to change in the record and save it using using either a Command or a Connection object. I prefer the Connection object. So you would have something like this:

[COLOR=blue]Public Sub[/color] SaveNewRecord()
[COLOR=blue]  Dim[/color] cn [COLOR=blue]As New[/color] ADODB.Connection
[COLOR=blue]  Dim [/color]sql [COLOR=blue]As String[/color]
[COLOR=blue]  Dim [/color]rs [COLOR=blue]As New[/color] ADODB.Recordset

  [COLOR=green]' Open the connection[/color]
  cn.ConnectionString = "YourConnectionString"
  cn.Open

  [COLOR=blue]On Error GoTo[/color] Err_Handler
  
  [COLOR=green]' Put everything in a transaction[/color]
  cn.BeginTrans

  [COLOR=green]' Get the record from database[/color]
  sql = "SELECT YourFields FROM YourTable..."
  rs.Open sql, cn, ...

  [COLOR=green]' Put your fields in variables[/color]
  [COLOR=blue]Dim[/color] field1 [COLOR=blue]As[/color] AppropriateType
  [COLOR=blue]Dim[/color] field2 [COLOR=blue]As[/color] AppropriateType
  [COLOR=green]...[/color]
  field1 = rs!Field1
  [COLOR=green]...[/color]

  [COLOR=green]' Change the key field[/color]
  field1 = NewValue

  [COLOR=green]' Save cahnged record to the database
  ' using an INSERT SQL statment[/color]
  sql = "INSERT INTO tbl([fields]...)" & _
  " VALUES (" & field1 & ", " & ... & ")"
  cn.Execute sql

  [COLOR=green]' Commit the transaction[/color]
  cn.CommitTrans
  [COLOR=blue]Exit Sub[/color]

Err_Handler:
  [COLOR=green]' Deal with your errors here...[/color]
  cn.RollbackTrans
  MsgBox "Could not save record!"
[COLOR=blue]End Sub[/color]

Hope this gets you going!

JC

Friends are angels who lift us to our feet when our wings have trouble remembering how to fly...

 

[CraigBest]

Just wanted to thank Simon and JC for the help. I used Simon's method and it worked great. Thanks very much. I'll look over JC's method too for the future.

Craig