Exporting Records To An Archive Database

[mr_e_landis]

I have been trying to figure out how to do this and the transferdatabase function wouldn't do what I needed. I have a table that has a bunch of records, each being a request for cad work somebody wants me to do. I change the status of each one to pending, working, or complete. Anyway I want to only keep the completed task records in the database for 90 days. After that the records will be exported to an archive database and purged from the working database. The problem I face is not getting the records I need “I used date stamps ” but getting the records I filtered to append the archive database table and not overwrite it. I made backups of my db and am testing on those and cant figure this out. Below is the code I used to export. Like I said it overides the table rather than append...

    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    "X:\Archive.mdb", acTable, "tRequest", _
    "tRequest"
    MsgBox "its done"

Any ideas, pointers, stern words and a link to a post I missed, anything to help is appreciated.
Thanks,
Eric

 

[mr_e_landis]

Wow. This was so easy to do it makes me sick that I had to ask for help ... An append and delete query did everything I needed to do... I just add a message box asking the user if they are sure then set "docmd.setwarnings false" and then open the append then the delete query, Done... Very simple...