Automatically Move Records 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.

 

[Spellman]

One way of doing it would be to have a field in the Table that keeps the date it was inserted so lets say it's called DateInserted and it defaults to todays date. You can do this by sticking in the getdate() function in the fields default value. Then to check if its six months old you might do something like this

if (datepart("mm",Now) - datepart("mm",InsertDate)) > 6 then
use insert statement here
end if

you will need to be familiar with ADO for this. If you'd like me to elaborate on the ADO bit please mail me back. Hope I wasn't too confusing.

 

[Garridon]

You can use two queries, an append query and a delete query (to delete the records from the existing table). You just set the criteria for a date field in both queries >Date()-180.

Then, on the event you want to trigger the queries to run (for instance, on the close of a form, so it runs each time), insert the following:

Private Sub Form_Close()
On Error GoTo Form_Close_Err

' Warnings off
DoCmd.SetWarnings False

' Append query
DoCmd.OpenQuery "qappName", acNormal, acEdit

' Delete query
DoCmd.OpenQuery "qdelName", acNormal, acEdit

' Warnings on
DoCmd.SetWarnings True


Form_Close_Exit:
Exit Sub

Form_Close_Err:
MsgBox Err.Description
Resume Form_Close_Exit

End Sub

Make sure you test it carefully, especially if you add any fields later on. If something is not correct in the append query, it can delete the record and not append it.
Linda Adams
Visit my web site for writing and Microsoft Word tips:

http://www.hackman-adams.com

http://www.david-hedison.com

 

[jbento]

Garridon,
Thanks for the code, but I didn't explain enough. That is my fault. This database is interacting with FRONTPAGE, so there is no form involved. The table will be connected to FRONTPAGE. I created the Append and Delete queries, but now how do I get this to work with FRONTPAGE. Where do I write the code? In FRONTPAGE or Access? If I have to write it in Access, where do I write the code, because there is no form for this, because FRONTPAGE only interacts with tables or queries. Let me know what do I do next, if you know. Your help will be appreciated. Thanks.

Jerome

 

[AccessGuru]

this can easily be done using an append query that you can call from a form or code which appends based on the date condition.