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.

re: part (1) - see Jeeped's answer below. re: part (2) - That's way too broad for a Stack Overflow question. Accept (one of) the answer(s) below, then get busy and write some VBA code. If you need help with that then review [ask] before you [ask a new question](http://stackoverflow.com/questions/ask). — Gord Thompson, Nov 16 '14 at 14:29

score 1 · Answer 1 · answered Nov 16 '14 at 14:18

1

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.

answered Nov 16 '14 at 14:18

1

Yes, that looks right to me. Full details in the MSDN article [here](http://msdn.microsoft.com/en-us/library/bb208861%28v=office.12%29.aspx). – Gord Thompson Nov 16 '14 at 14:19
1

@GordThompson - +1 Thanks for confirming that code. I was also thinking that a `SELECT INTO` may be more appropriate if the target archive table has not already been created. [SELECT...INTO Statement](http://office.microsoft.com/en-us/access-help/select-into-statement-HP001032266.aspx) – Nov 16 '14 at 14:24
Yup, you are correct there too. `INSERT INTO (...) SELECT ...` is what Access calls an "Append query" and `SELECT ... INTO ...` is what Access calls a "Make-table query". – Gord Thompson Nov 16 '14 at 14:27
Don't forget to use proper error handling so the data is not deleted until a successful INSERT INTO is completed. This statement is directed at the OP, not Jeeped. – Johnny Bones Nov 16 '14 at 15:56
@JohnnyBones How can I check that insert into select is successful with no error? How Can I make sure that all or no data transferred as the result of insert into ... select command? – mans Nov 16 '14 at 19:39
You could probably do a recordcount before appending the new archive records, a recordcount after they're archived, and compare the two to a recordcount of all records that **should** be archived. The only unfortunate thing is that I don't believe Access has any type of Rollback command like SQL Server does. So, if those counts don't match, I don't know if you can reverse it anyway. – Johnny Bones Nov 16 '14 at 22:21

score 1 · Accepted Answer · edited May 23 '17 at 12:03

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

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

2 Answers2