Regularly archive Access database records

A common database task archives records from one table to another. If you want to delete unnecessary records from the original table to reduce the table size, but may refer to these records in the future, you can archive records (instead of deleting records ). To archive records in the MicrosoftAccess database, use a macro to run the query and extract records from a table

A common database task archives records from one table to another. If you want to delete unnecessary records from the original table to reduce the table size, but may refer to these records in the future, you can archive records (instead of deleting records ). To archive records in the Microsoft Access database, use a macro to run the query and extract records from a table

A common database task archives records from one table to another. If you want to delete unnecessary records from the original table to reduce the table size, but may refer to these records in the future, you can archive records (instead of deleting records ). To archive records in the Microsoft Access database, use a macro to run the query and move the records from one table to another.

Note that the following process uses the example in the Rose sample database. The default location of the Rose sample database is the Program Files \ Microsoft Office \ Office10 \ Samples folder. If your Samples Folder does not contain any Northwind. mdb file, or you need to open the rose text details, please enter "Open the rose text Sample Database" in the Office assistant or the "response wizard" tab in the "help" window ", click search ".

Regularly archive records from one table to another

1. Run the copy and paste commands to copy the table to be archived to the new table. Only copy structure, not data.

For example, copy the structure of the "product" table to a new table named "Product Archive.

2. Create a query to include all the fields in the "product" table and add the records to the "Product Archive" table. Set conditions to specify a subset of records to be archived.

For example, to create an additional query named "Product appending", set the "condition" cell of the "Abort" field to-1, you can only append the aborted entry to the product archive table.

Note: If the conditions for each archive change, you can create an additional query with the prompt parameter so that you can enter different conditions each time you run the query.

3. Create a delete query. The query conditions are the same as those specified for the additional query in step 2. You will use this query to delete the records attached in step 2 from the products table.

For example, if you create a "delete product" query, the query conditions are the same as those specified in the "Additional Product" query.

Note: If you create an additional query that prompts you to enter parameters, you should also create a deletion query that prompts you to enter the same parameters, and the parameter prompt appears for each running query, make sure to enter the same value.

4. Create a macro to run the additional query and then run the delete query.

For example, create a macro named "Product additional record", use the OpenQuery operation in the first row of the macro to run the additional query, and use the OpenQuery operation in the second row of the macro to run the delete query operation.

5. Run the "Additional Product records" macro every time you archive records.