archieving data

[Guest]

i have a large database which we are presently adding 1000 records to a day.
After 3 months of use the table named [jobs] is getting rather large making
it difficult to search.

the table has the following fields:

[jobs]: ref no, job no, surname, clinic, department, completion date

I would like some way of getting it to automatically archieve jobs that have
been complete for more than 3 months.

Does any one have an idea of how to do this? i would prefer some kind of
macro that automatically run when the program is opened or before it is
close. any help would be appreciated.

 

[Ron Hinds]

First create a new table named "JobsArchive" with the same field names/data
types as your original table. Then, in the Close event of your form, add
this code:

Private Sub Form_Close()

Dim db As Database
Dim strSQL As String

strSQL = "INSERT INTO JobsArchive ([ref no], [job no], [surname],
[clinic], [department], [completion date])" _
& " SELECT [ref no], [job no], [surname], [clinic], [department],
[completion date] FROM Jobs" _
& " WHERE [completion date] < Date - 90"
db.Execute strSQL

strSQL = "DELETE FROM Jobs WHERE [completion date] < Date - 90"
db.Execute(strSQL)

End Sub

You should also be compacting your database regularly.