You have 20 million records in some tables?
If so, you will want to:
a) Create a new (blank) database to use as the arvhive.
Same tables (structure only.)
b) Figure out a cut-off date for archiving.
c) Make decisions about how to duplicate data between the current db and the
archive. (For example, if the cutoff is 5 years ago, any customer who has
newer and older records will be in both, but anyone who has no orders in the
last 5 years will not be in the current database?)
d) Examine the relationships between tables, and design a strategy that
takes the dependencies into account. For example, order details must be
handled before orders, and orders before customers (since you cannot delete
the old orders until the order details are gone, and you cannot delete the
customers until their orders are gone.) Be especially aware of any cascading
deletes: archive in the wrong order here, and you lose data. (JET is not
always good at preventing dependencies in cascades.)
e) Figure out the amount of data being executed. It would be nice to do the
whole archive in a transaction, but it may be too large to process (or too
inefficient.) It might be better to make a backup of the data file instead,
so you have a physical rollback point rather than a JET transaction.
f) Write the queries. If this is something you will do periodically, you can
do this as a VBA routine: a form that makes a copy of the structure-only
database (from (a) above), executes each of the Append queries to add the
archive data, and then executes each of the delete queries to remove the old
data from the current database.
g) Compact the current database, to regain the space.
In the end you have 3 databases:
- the archive, with only the old records;
- the current database, with current records (perhaps overlapping some
archived ones);
- the backup (with both), in case anything went wrong.
