Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Posts
    42

    Arrow Unanswered: Appending Data to existing table

    I want to allow the user to update a newer version of a db with data kept in an old version of a db.

    I want to maintain AutoNumbered Primary keys etc, also.

    The new db will be totally void of data to start with.

    What I have been doing in the past is maually copying the table from the old db and then pasting it to the newer version selecting "Append to existing table".

    I am trying to do the same in code but am having trouble finding something that will append data only (including AutoNumbered PK) to a newer db.

    I have tried appending the table to the new db but given they have the same name, it does not work.


    Also, the newer Table may have some extra fields in it, but all the old fields are still present.


    Any suggestions?

  2. #2
    Join Date
    Apr 2003
    Posts
    42

    The longway...

    I am copying them accross field by field, record by record....

    Dunno if this is the only way.

    Still can find a function that will append to existing table.

  3. #3
    Join Date
    Aug 2003
    Location
    Toronto, Canada
    Posts
    10
    Why don't you try copying your data to a temporary table with the same structure and then append it from there? All of this can be done in code so wouldn't have to do it manually.

    I hope I'm understanding what you want to do correctly...

    Peter

  4. #4
    Join Date
    Jul 2003
    Posts
    38
    Peter, why do you want to use a temporary table? He jsut needs to open the old table and copy field by field, like this:

    public sub CopyTables ( oldDB as string, TableName as string)

    ...

    dim db as dao.database
    dim oldRS as dao.recordset
    dim newRS as dao.recordset
    dim i as integer

    set db=opendatabase(oldDB)
    set oldrs=db.openrecordset(Tablename,dbopendynaset)

    ...

    if not oldrs.eof then oldrs.movelast
    if not oldrs.bof then oldrs.movefirst

    while not oldrs.eof
    newrs.addnew
    for i=0 to oldrs.recordcount
    newrs.fields(oldrs.fields(i).name)=oldrs.fields(i)
    next i
    oldrs.movenext
    newrs.update
    wend

    ...closing rs and db...

    end sub

    Hope this was helpful...

  5. #5
    Join Date
    Aug 2003
    Location
    Toronto, Canada
    Posts
    10
    your way is better...

  6. #6
    Join Date
    Apr 2003
    Posts
    42

    Thanks...

    Thanks for the comments...

    I did it similar to SubHugo's suggestions except I had to use the fieldnames themselves rather than their index numbers.

    I did this cos there is a possibility that some of the tables may have gained or lost some fields between the different db versions.

    Thanks again.

  7. #7
    Join Date
    Jul 2003
    Posts
    38
    Just to correct you - i used the field names ;-)

    newrs.fields(oldrs.fields(i).name)=oldrs.fields(i)
    ^^^^

    But my version has the advantage not to hardcode the fieldnames...

    regards

Posting Permissions

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