[RESOLVED] How To Copy A Record in Database

[JohnAtom]

Hi everyone,

I used the following format of code to access database, can anyone tell me how to duplicate a record :

    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set CN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
    
    CN.Open "dbclient"
    RS.Open "select * from clientstyle where clientstyleclient='" & strClientCode & "' and clientstylestyle='" & strStyleCode & "'", CN, adOpenDynamic, adLockPessimistic
    
    If RS.EOF Then

    End If

    RS.Close
    CN.Close

    Set RS = Nothing
    Set CN = Nothing

Thank You In Advance

[Capp]

1 way to do that is to create 2 Recordsets.

Use the first one to access the record you want to duplicate and use the second one to create the record based on the first ones' query

Dim RS1 as ADODB.RecordSet
Dim RS2 as ADODB.RecordSet
 
Set RS1 = New ADODB.RecordSet
Set RS2 = New ADODB.RecordSet
 
RS1.Open ("SELECT * FROM Table), constring
RS2.Open ("SELECT * FROM SameTable), constring
 
With RS2
    .AddItem
          .Fields("FieldName") = RS1.Fields("FieldName")
    .Update
End With
 
Set RS1 = Nothing
Set RS2 = Nothing

Something like that.

[JohnAtom]

Capp,

Thank you for your quick respond.

But this line ".Fields("FieldName") = RS1.Fields("FieldName")" doesn't work, it come back with RUN-TIME ERROR "-214727887".

And at the same time, is there a way to handle the "FieldName" quicker because my recordset will have more than 50 data fields.

[Capp]

Capp,

Thank you for your quick respond.

But this line ".Fields("FieldName") = RS1.Fields("FieldName")" doesn't work, it come back with RUN-TIME ERROR "-214727887".

And at the same time, is there a way to handle the "FieldName" quicker because my recordset will have more than 50 data fields.

I do apologize if there is a syntax error. I don't have VB installed on this machine.

One thing you can do to check is let VB use the autocomplete feature. When you type RS1 or RS2 and press the "." button, see what is closest to "Fields"....it might just be "Field" (without the 's').

If you are wanting to speed it up a bit, you can do something like this:

With RS1
.Field("Fieldname") = Whatever
.Field("FieldName2") = Whatever2
End With

I hate to give you this incorrectly but I think you can use the "!" symbol to replace RS as well.

[JohnAtom]

Capp,

This is how I handle the copy and it work fine in my application:

    
    Dim g As Integer
    Dim RS2 As ADODB.Recordset

    Do While Not RS.EOF
        Set RS2 = RS.Clone
        RS2.AddNew
        For g = 1 To RS.Fields.Count - 1
            RS2(g) = RS(g)
        Next g
        RS2.Update
        Set RS2 = Nothing
        RS.MoveNext
    Loop

[Capp]

Capp,

This is how I handle the copy and it work fine in my application:

Well I am glad you got it working. I don't know how much help I actually was, but at least you figured it out and thanks for sharing the code. I appreciate it

[Al42]

To add a record, you'd say:
[Highlight=VB]
Dim strSQL As String

strSQL = "Insert Into <tablename> (<Field1Name>, <Field2Name>,<etc>) Values(" & RS1.Fields(0).Value & ", '" & RS1.Fields.(1).Value & "', ...)"
ConnectionToRS2.Execute strSQL
[Highlight=VB]
You could build the string in code in a For loop of 0 to 49 if you have 50 fields, to shorten the code a bit (get the field names from RS1.Fields(i).Name and the value from RS1.Fields(i).Value).