How Do I: Move (cut and paste) a record from one DB table to another DB table?

[RichardAnderson]

Hello folks,

This may not be possible. But I really hope it is. If it is, then I will need instructions on how to do it. I am not very good at code.

Database1 is named DPR-Main. It has a table called tblBuilds. Also in the DB are alot of forms for reading this table in different views. When a project (record) is complete, a date is placed in the completed field.
Database2 is named DPR-Completed-2013. In this I have a table called tblBuilds_Completed_2013.

This is what I would like to happen....

When the date is entered in the completed field of tblbuilds of database1, I want that record to be copied over to the table tblBuilds_Completed_2013 in database2. And if it is possible, the original record in tblbuilds should be deleted. The reason for this to keep the database sizes reasonable.



I am however concerned about 1 thing is this indeed possible. Would the original records ID also be copied over?

[pbaldy]

Well, the "how" would generally be to link to both tables and then run an append query and a delete query with the appropriate criteria. That said, unless you were dealing with millions of records, I wouldn't archive them. Even if you were, I'd be more likely to look at a more robust back end to store the data than archive records.

[ssanfu]

Would the original records ID also be copied over?

It could be... what is the field type? Is it an autonumber? If so, the field would have to be changed to a Long in the archive table.

Are there any related (linked) tables? That would be a consideration.

As Paul said, I wouldn't move the record to a different BE. And having a year in the table name does not conform to normalization rules.
I would have an archive field, possible a Y/N field. Filter out any record where the archive field is TRUE. You could then look at completed records without opening a different dB.