Automatic Move Record to Another Table

[jbento]

Hi All,
I hope someone can help me with this. I have a simple table (table 1), and in that table there is a date field. I have another table that has the same fields, but it is my archive table (table 2). I would like for table one to automatically move a record to table 2, once that record is 6 mths. old, or any timeframe I specify. Anyone know how to do that automatically, with code? I would appreciate any help anyone can give. Thanks so much.

I hope everyone had a great THANKSGIVING, and GOD bless to you all.

 

[MichaelRed]

You need to have an event trigger the process, but the actual "move" is rather trivial.

Begin Transaction

Append all records to the &quot;archivedb&quot; from the &quot;on-linedb&quot; where [i]datefield[/i] <= DateAdd(&quot;m&quot;, 6, Date)

Delete All Records from &quot;on-linedb&quot; where [i]datefield[/i] > DateAdd(&quot;m&quot;, 6, Date)

End Transaction

Not that the above peudocode is only useful on a once-a-month interval. If you want to do this more often, the date
calculation in date add can get to be convoluted - depending on how "exact" you need the six months to be.

The append and delete "statements" can (SHOULD BE) be plain ole queries, but this means that you need to declare and instantiate the db and the queries, then execute them. You also need error trapping code (esp for the transaction), and to do a roll back if an error occurs.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over