Results 1 to 5 of 5

Thread: Copy Records

  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Copy Records

    hi,
    want a function which will copy the last record in a table and make "n" number of duplicates of that record; each nth record has to have a unique id

    Here's my prototype

    Dim conn as ADODB.Connection
    Dim rec as ADODB.Recordset

    set conn = Currentproje....
    set rec = new....

    rec.Open "SELECT * FROM [IDS LIST] DESC", ......

    (Do i copy Field for field into the new record? Like .AddNew !FieldName
    or is there a way to simply copy record over?)

    Thx

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    What I would do is to append the records "n" number of times to a temporary table using an Append query, and then sort out the Primary Key afterwards. This is a lot simpler (and a lot quicker) than trying to make physical copies of a record through code.

    I hope this helps!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Thx for the suggestion; unfortunately, i'm the DB admin at my depart. so those who log on to this DB dont know much about the workings of tables or queries;

    I was wondering if there was a one click soln for them with me writing the nitty gritty code behind it;

    maybe like a

    rec.Addnew

    for i: 1 to rec.Fields.End or something
    rec.Fields(i) = rec(i).Value //copy values over
    next

    hey! maybe that for..next might work...let me try that

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Yes, your method would of course work. It is slow for a lot of fields however. You can still use queries from code : an example is below.


    Function CopyRecords(CountOfRecords As Integer)

    Dim x as integer

    For x = 1 to CountOfRecords

    docmd.openquery "MyAppendQuery"

    next x

    end function


    This would run the append query the number of times stored in the CountofRecords parameter. To use the function, you could assign it to a button and then call the function with CopyRecords(5) for example, which would make 5 copies of the records from your source table to your destination table.
    You'd probably want to put a DoCmd.SetWarnings False in there as well to stop Access asking the user to confirm the append each time.

    Hope this helps!!
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    var temporary as variant
    rec ->adodb.recordset
    conn->adodb.connection, currentproject.conn.

    rec.open ,.....

    For Each fld In rec.Fields
    count = count + 1
    Next

    rec.AddNew

    For i = 1 To (count - 1)
    If i <> 17 Then //a field i just didn't want to copy
    rec.MovePrevious
    temporary = rec(i).Value
    rec.MoveNext
    rec(i).Value = temporary
    End If
    Next

    i tried your method too, it was pretty nice!
    Thx for the help

Posting Permissions

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