Automating Archiving Records in Microsoft Access, using Append and Delete Queries:

When you deal with a lot of Microsoft Access data, involving many records in your database tables, you may not always wish to have all of that data stored in the one main table, that is accessed on a regular basis. You may need to regularly archive your Access data so that you are only dealing with the most current up to date records.

This process can be done quite easily by manually running an Append Query to copy the records to the archive table and then by running a Delete Query to remove the records from the main table.

You can see the process of creating the Append Query in the Creating an Append Query in Microsoft Access article and the Delete query in the Creating a Delete Query in Microsoft Access article, where records are deleted once they are deemed to be past a certain date limit.

This article will show you how to automate the Archive process meaning that you will not need to manually run these queries, ensuring that you are always dealing with the latest data in your Microsoft Access table.

To automate the Microsoft Access archive, you can create a procedure that will run automatically. This procedure could be run from a command button or could be run on Opening the Database, there are various ways to produce this, however the article will show the code required to run the process from a command button place on a form.

From the example form below, you will see that it contains the Run Archive command button, which is what will run the append and delete routine:

The code used behind this button, activated from the On Click event is as follows:

Private Sub cmdArchiveData_Click()
'Run Archive - Append and Delete

    Dim strSQLAppend As String
    Dim strSQLDelete As String
    Dim errLoop As Error
    Dim dteExpiry As Date

    dteExpiry = DateAdd("yyyy", -2, Date)

    ' Define two SQL statements for action queries.
    strSQLAppend = "INSERT INTO tblExpiredStudents " & _
                   "( strStudentID, strFirstName, strLastName, strAddress1, " & _
                   "strAddress2, strCity, strCounty, strPostCode, strTelephone, " & _
                   "[hypE-mailAddress], dtmDOB, dtmEnrolled, strCourseID ) " & _
                   "SELECT tblStudentInformation.strStudentID, " & _
                   "tblStudentInformation.strFirstName, " & _
                   "tblStudentInformation.strLastName, " & _
                   "tblStudentInformation.strAddress1, " & _
                   "tblStudentInformation.strAddress2, " & _
                   "tblStudentInformation.strCity, " & _
                   "tblStudentInformation.strCounty, " & _
                   "tblStudentInformation.strPostCode, " & _
                   "tblStudentInformation.strTelephone, " & _
                   "tblStudentInformation.[hypE-mailAddress], " & _
                   "tblStudentInformation.dtmDOB, " & _
                   "tblStudentInformation.dtmEnrolled, " & _
                   "tblStudentInformation.strCourseID " & _
                   "FROM tblStudentInformation " & _
                   "WHERE tblStudentInformation.dtmEnrolled <= #" & dteExpiry & "#;"


    strSQLDelete = "DELETE tblStudentInformation.strStudentID, " & _
                   "tblStudentInformation.strFirstName, " & _
                   "tblStudentInformation.strLastName, " & _
                   "tblStudentInformation.strAddress1, " & _
                   "tblStudentInformation.strAddress2, " & _
                   "tblStudentInformation.strCity, " & _
                   "tblStudentInformation.strCounty, " & _
                   "tblStudentInformation.strPostCode, " & _
                   "tblStudentInformation.strTelephone, " & _
                   "tblStudentInformation.[hypE-mailAddress], " & _
                   "tblStudentInformation.dtmDOB, " & _
                   "tblStudentInformation.dtmEnrolled, " & _
                   "tblStudentInformation.strCourseID " & _
                   "FROM tblStudentInformation " & _
                   "WHERE tblStudentInformation.dtmEnrolled <= #" & dteExpiry & "#;"



    ' Run action query to restore data. Trap for errors,
    ' checking the Errors collection if necessary.
    On Error GoTo Err_Execute
    CurrentDb.Execute strSQLAppend, dbFailOnError
    CurrentDb.Execute strSQLDelete, dbFailOnError
    On Error GoTo 0

    Exit Sub

Err_Execute:

    ' Notify user of any errors that result from
    ' executing the query.
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & vbCr & _
                   errLoop.Description
        Next errLoop
    End If

    Resume Next

End Sub

This checks the data in the Microsoft Access table (tblStudentInformation), to see if there are any records older than two years. If so, it then runs the INSERT INTO statement, that will copy the data from this table to the Expired Students table.

Once it has copied the data between the tables, it then runs the DELETE statement, to remove the data from the original table, as it is no longer required in that database table.

To see this example in action, you can download the Microsoft Access 2000 Database Sample file from either the Microsoft Access Forms page or the Microsoft Access Downloads page.

Please Note: The dates contained in the downloadable example database may need to be modified to ensure that you see records older than two years prior to today's date.