Student Record Archiving in Microsoft Access

Submitted by rinvizle on Thursday, July 14, 2016 - 08:06.
In this tutorial we will create Student Record Archieving in MSAccess. 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. In this process can be quite done 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. I have Example Images and Code below.

Sample Code

Script for the Records
  1. Option Compare Database
  2.  
  3. Private Sub cmdArchiveData_Click()
  4.  
  5.     Dim strSQLAppend As String
  6.     Dim strSQLDelete As String
  7.     Dim errLoop As Error
  8.     Dim dteExpiry As Date
  9.  
  10.     dteExpiry = DateAdd("yyyy", -2, Date)
  11.  
  12.     strSQLAppend = "INSERT INTO tblExpiredStudents " & _
  13.                    "( strStudentID, strFirstName, strLastName, strAddress1, " & _
  14.                    "strAddress2, strCity, strCounty, strPostCode, strTelephone, " & _
  15.                    "[hypE-mailAddress], dtmDOB, dtmEnrolled, strCourseID ) " & _
  16.                    "SELECT tblStudentInformation.strStudentID, " & _
  17.                    "tblStudentInformation.strFirstName, " & _
  18.                    "tblStudentInformation.strLastName, " & _
  19.                    "tblStudentInformation.strAddress1, " & _
  20.                    "tblStudentInformation.strAddress2, " & _
  21.                    "tblStudentInformation.strCity, " & _
  22.                    "tblStudentInformation.strCounty, " & _
  23.                    "tblStudentInformation.strPostCode, " & _
  24.                    "tblStudentInformation.strTelephone, " & _
  25.                    "tblStudentInformation.[hypE-mailAddress], " & _
  26.                    "tblStudentInformation.dtmDOB, " & _
  27.                    "tblStudentInformation.dtmEnrolled, " & _
  28.                    "tblStudentInformation.strCourseID " & _
  29.                    "FROM tblStudentInformation " & _
  30.                    "WHERE tblStudentInformation.dtmEnrolled <= #" & dteExpiry & "#;"
  31.  
  32.  
  33.     strSQLDelete = "DELETE tblStudentInformation.strStudentID, " & _
  34.                    "tblStudentInformation.strFirstName, " & _
  35.                    "tblStudentInformation.strLastName, " & _
  36.                    "tblStudentInformation.strAddress1, " & _
  37.                    "tblStudentInformation.strAddress2, " & _
  38.                    "tblStudentInformation.strCity, " & _
  39.                    "tblStudentInformation.strCounty, " & _
  40.                    "tblStudentInformation.strPostCode, " & _
  41.                    "tblStudentInformation.strTelephone, " & _
  42.                    "tblStudentInformation.[hypE-mailAddress], " & _
  43.                    "tblStudentInformation.dtmDOB, " & _
  44.                    "tblStudentInformation.dtmEnrolled, " & _
  45.                    "tblStudentInformation.strCourseID " & _
  46.                    "FROM tblStudentInformation " & _
  47.                    "WHERE tblStudentInformation.dtmEnrolled <= #" & dteExpiry & "#;"
  48.  
  49.     On Error GoTo Err_Execute
  50.     CurrentDb.Execute strSQLAppend, dbFailOnError
  51.     CurrentDb.Execute strSQLDelete, dbFailOnError
  52.     On Error GoTo 0
  53.  
  54.     Exit Sub
  55.  
  56. Err_Execute:
  57.  
  58.     If DBEngine.Errors.Count > 0 Then
  59.         For Each errLoop In DBEngine.Errors
  60.             MsgBox "Error number: " & errLoop.Number & vbCr & _
  61.                    errLoop.Description
  62.         Next errLoop
  63.     End If
  64.  
  65.     Resume Next
  66.  
  67. End Sub
  68.  
  69. Private Sub cmdExpiredStudents_Click()
  70.     DoCmd.OpenTable "tblExpiredStudents"
  71. End Sub
  72.  
  73. Private Sub cmdOpenStudentTable_Click()
  74.     DoCmd.OpenTable "tblStudentInformation"
  75. End Sub
  76.  
  77. Private Sub lblClose_Click()
  78.     DoCmd.Close acForm, "frmArchive"
  79. End Sub
Result Don't forget to Like and Share. Enjoy Coding.
Tags
msaccess
add record

Add new comment

About text formats
  • 44 views