Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2014
    Posts
    3

    Unanswered: Copy Record From DBSrc RecordSet to DBSest RecordSet

    I have a source database and a destination database. At a point in time, the destination DB resulted from making a copy of the source DB's .accdb file. (Destination is current, Source is "archival.")

    Additionally, I have a code DB that opens two RecordSets, one on a table in the SourceDB, the other on the same-named table in the DestinationDB.

    During processing, I come to a record in the SourceDB that I want to make a copy of in the Destination DB.

    I have opened the destination RecordSet "..., dbOpenDynaset, DB_APPENDONLY"
    I have opened the source RecordSet "..., dbOpenSnapshot". I will be moving forward only through the Source RecSet.

    Do I have only the following, as a choice?

    Code:
        DstRS.AddNew
        DstRS.Fields("F_one").value = SrcRS.Fields("F_one").value
        DstRS.Fields("F_two").value = SrcRS.Fields("F_two").value
        . . .
    
        DstRS.Fields("F_n").value = SrcRS.Fields("F_n").value
        DstRS.Update

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Is there any reason why you can't use an Append query ?

    MTB

  3. #3
    Join Date
    Dec 2014
    Posts
    3

    On using an Append Query

    Quote Originally Posted by MikeTheBike View Post
    Hi

    Is there any reason why you can't use an Append query ?

    MTB
    Given that I have the data to be copied in one recordset, a reference to the destination in another, and that I am already running VBA to locate the records to be copied, it seems that using an append query would add an awkward step building the string that will be used for the append query. I have to build, field by field, the destination fields, then do that again for the source fields. (It would have to be

    INSERT INTO tbl (FLD1, FLD2, ..., Fldn)
    VALUES (v1, v2, ..., vn);

    Unfortunately, I do not have a simple set of conditions that identify the records to be copied, so a static append query, with a SELECT clause won't do the trick.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Brian

    As you seem to have a pretty good handle the possible methods, I do not see what sort of alternative solution you are expecting.

    However, if the fields names were F_1, F_2, F_3 etc., then looping through the fields upto the Fields.Count would be possible using the loop counter as a variable to concatonate into the field name!

    MTB

  5. #5
    Join Date
    Dec 2014
    Posts
    3
    Quote Originally Posted by MikeTheBike View Post
    Hi Brian

    As you seem to have a pretty good handle the possible methods, I do not see what sort of alternative solution you are expecting.

    However, if the fields names were F_1, F_2, F_3 etc., then looping through the fields upto the Fields.Count would be possible using the loop counter as a variable to concatonate into the field name!

    MTB
    Naturally, if those were the field names . . . (But I would never do that! )

    I was hoping there was some copyRecord sort of feature that I was unaware of.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •