How to write a SQL command to move all old data from access table into another database/table?

Question

I have a table in MS Access 2003 in which I want to archive all old data.

The criteria is that the creation data should be less than a specific date.

I can write a SQL statement to select them, but I don't know how to move them to another database/table? Assuming that the archive database/table is already created and data structure matches current table.

Also how I can make sure that all data which is moved to archive table is removed from current table?

I want to write VBA code to run the command check that data is archived correctly.

Answer 1

You want to 1) move data meeting certain criteria from one table to another, existing table with the same format. 2) You want to "make sure that all data which is moved to archive table is removed from current table." And 3) you "want to write VBA code to run the command check that data is archived correctly."

Contrary to popular opinion, Access does support transactions (the claim that Access SQL does not support transactions is true, but we can still use transactions in VBA code). So modifying code in this post to use transactions in a workspace, I believe this would do the trick (tested in Access 2010 using DAO).

The code to lock, get counts and unlock is not really necessary, and may increase the difficulty of implementing the archive, since it will require that no one be writing to the table while you're updating it. And if it did find a problem, Access does not support transaction logging, so you would have a very short list of options as to how to fix it. But it sounded like you wanted to be absolutely sure the counts were correct, so this adds another level, arguably unnecessary, of checking.

Option Compare Database
Option Explicit

Sub ArchiveOldRecords()

Dim nSourceCount As Long, nMoveCount As Long, nDestCount As Long
Dim strSQL As String, sMsg As String
Dim rsLock As DAO.Recordset
Dim rsBefore As DAO.Recordset, rsAfter As DAO.Recordset
Dim wrk As Workspace, db As DAO.Database

Const strcTableSource As String = "t_TestWithDate"  ' Move records FROM table
Const strcTableArch As String = "t_ArchiveTestWithDate" ' Move records TO table
Const strcWHERE As String = " WHERE field2 < " _
    & "DATEADD(""yyyy"", -1, Date())"   ' Select date field and DATEADD params
Const strcCount As String = "SELECT COUNT(*) As "

On Error GoTo TrapError
    Set db = CurrentDb
    Set wrk = DBEngine.Workspaces(0)
' Lock table - so no one can add/delete records until count is verified
    Set rsLock = db.OpenRecordset(strcTableSource, dbOpenTable, dbDenyWrite)
' Get initial table counts
    Set rsBefore = db.OpenRecordset( _
        strcCount & "SourceCount, " _
        & "(SELECT COUNT(*) FROM " & strcTableSource _
            & strcWHERE & ") As MoveCount, " _
        & "(SELECT COUNT(*) FROM " & strcTableArch & ") As DestCount " _
        & "FROM " & strcTableSource & ";", dbOpenForwardOnly)
    nSourceCount = rsBefore!SourceCount
    nMoveCount = rsBefore!MoveCount
    nDestCount = rsBefore!DestCount
    rsBefore.Close

    wrk.BeginTrans
    ' Copy records
        strSQL = "INSERT INTO " & strcTableArch _
            & " SELECT * FROM " & strcTableSource & " " & strcWHERE & ";"
        db.Execute strSQL, dbFailOnError
    ' Unlock table - only needed for counts
        rsLock.Close
        Set rsLock = Nothing
    ' Delete copied records
        strSQL = "DELETE * FROM " & strcTableSource & " " & strcWHERE & ";"
        db.Execute strSQL, dbDenyWrite + dbFailOnError
    ' Lock table - only needed for counts
        Set rsLock = db.OpenRecordset(strcTableSource, dbOpenTable, dbDenyWrite)
    wrk.CommitTrans

    ' Get final table counts
    Set rsAfter = db.OpenRecordset( _
        strcCount & "SourceCount, " _
        & "(SELECT COUNT(*) FROM " & strcTableSource _
            & strcWHERE & ") As MoveCount, " _
        & "(SELECT COUNT(*) FROM " & strcTableArch & ") As DestCount " _
        & "FROM " & strcTableSource & ";", dbOpenForwardOnly)

    ' Double-check counts
    If (rsAfter!SourceCount <> nSourceCount - nMoveCount) _
        Or (rsAfter!DestCount <> nDestCount + nMoveCount) _
        Or (rsAfter!MoveCount > 0) Then
        sMsg = vbNewLine
        sMsg = sMsg & "Records in " & strcTableSource & " before:            "
        sMsg = sMsg & nSourceCount
        sMsg = sMsg & vbTab & "after: "
        sMsg = sMsg & rsAfter!SourceCount
        sMsg = sMsg & vbNewLine
        sMsg = sMsg & "Records to archive from " & strcTableSource & ": "
        sMsg = sMsg & nMoveCount
        sMsg = sMsg & vbTab & "after: "
        sMsg = sMsg & rsAfter!MoveCount
        sMsg = sMsg & vbNewLine
        sMsg = sMsg & "Records in " & strcTableArch & " before:       "
        sMsg = sMsg & nDestCount
        sMsg = sMsg & vbTab & "after: "
        sMsg = sMsg & rsAfter!DestCount
        MsgBox "Count double-check failed" & sMsg
    End If

Exit_Sub:
    On Error Resume Next
    ' Unlock table and close recordsets
    rsLock.Close
    rsBefore.Close
    rsAfter.Close
    Set rsBefore = Nothing
    Set rsAfter = Nothing
    Set rsLock = Nothing
    Set db = Nothing
    Set wrk = Nothing
    Exit Sub

TrapError:
    MsgBox "Failed: " & Err.Description
    wrk.Rollback
    Err.Clear
    Resume Exit_Sub

End Sub

Answer 2

There is no MOVE command but you can copy the records across to the target and then use a similar query to remove them from the source when you are sure you have no Paste Errors.

INSERT INTO MyArchive (fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3, fld4 FROM MyTable WHERE fld4 < DATEADD("y", -5, Date())

That copies across everything older than 5 years. After confirming the transfer,

DELETE * FROM MyTable WHERE fld4 < DATEADD("y", -5, Date())

That's off the top of my head and I transition between T-SQL and MS Access a fair bit but I think that is pretty solid Access query code. Your own field lists will vary accordingly.