| A common database task is archiving records from one table to
another. You archive when you want to remove unnecessary records from the
original table to reduce its size, but you realize there is a chance you will
need to access these records in the future. To archive records in a
Microsoft Access database, we'll use a macro to run the necessary queries to
move records from one table to another.
The following procedure uses the Northwind sample database. The default
location of the Northwind sample database is the Program Files\Microsoft
Office\Office 10\Samples folder. If you have any trouble finding it, just
type into the Office Assistant or into the Help window open Northwind sample
database.
Here we go:
-
Use the Copy and Paste commands to make copies of the tables Order Details and
Orders. Be sure to copy only the structures, not the data. Name these new
tables Order Details_Archive and Orders_Archive.
-
Create an append query that contains all the fields in the Order Details table
and appends records to the Order Details_Archive table. Set the criteria
to specify the subset of records you want to archive; where Order ID is less
than 10260, for example.
|
 |
-
Do the same for the table on the one side of the relationship - append records
from the Orders table to the Orders_Archive table where Order ID is less than
10260.
-
Create delete queries with the same criteria you specified in the append
queries.
TIP: If your criteria changes each time, make the append queries and
delete queries be parameter queries that prompt the user.
-
Create a macro that runs the append queries and then the delete queries. Be
sure to delete records from the Order Details table (because it's on the many
side of the relationship) before deleting the records from the Orders table.
For example, create a macro called OrdersArchive that uses the OpenQuery action
(twice) to run the append queries, and the OpenQuery action (twice again) to
run the delete queries. For added pizzazz, add a MsgBox action that
alerts you that the archive task is complete.
-
Run the macro each time you want to archive.
|
|
|