Microsoft Access Tips for Serious Users

Provided by Allen Browne, January 2002.

Archive: Move Records to Another Table

A move consists of two action queries: Append and Delete. A transaction blocks the Delete if the Append did not succeed. Transactions are not difficult, but there are several pitfalls.

Should I archive?

Probably not. If possible, keep the old records in the same table with the current ones, and use a field to distinguish their status. This makes it much easier to query the data, compare current with old values, etc. It's possible to get the data from different tables back together again with UNION statements, but it's slower, can't be displayed as a graphic query, and the results are read-only.

Archiving is best reserved for cases where you won't ever need the old data, or there are overriding considerations e.g. hundreds of thousands of records, with new ones being added constantly. The archive table will probably be in a separate database.

The Steps

The procedure below consists of these steps:

1. Start a transaction.
2. Execute the append query.
3. Execute the delete query.
4. Get user confirmation to commit the change.
5. If anything went wrong at any step, roll back the transaction.

The Traps

Watch out for these serious traps when working with transactions:

1. Use dbFailOnError with the Execute method. Otherwise you are not notified of any errors, and the results could be incomplete.
2. dbFailOnError without a transaction is not enough. In Access 95 and earlier, dbFailOnError rolled the entire operation back, and the Access 97 help file wrongly claims that is still the case. (There is a correction in the readme.) FromAccess 97 onwards, dbFailOnError stops further processing when an error occurs, but everything up to the point where the error occurred is committed.
3. Don't close the default workspace! The default workspace--dbEngine(0)--is always open. You will set a reference to it, but you are not opening it. Access will allow you to close it, but later you will receive unrelated errors about objects that are no longer set or have gone out of scope. Remember: Close only what you open; set all objects to nothing.
4. CommitTrans or Rollback, even after an error. The default workspace is always open, so an unterminated transaction remains active even after your procedure ends! And since Access supports multiple transactions, you can dig yourself in further and further. Error handling is essential, with the rollback in the error recovery section. A flag indicating whether you have a transaction open is a practical way to manage this.

The Code

This example selects the records from MyTable where the field MyYesNoField is Yes, and moves them into a table named MyArchiveTable in a different database file - C:\My Documents\MyArchive.mdb.

Note: Requires a reference to the DAO library.

Sub DoArchive()
On Error GoTo Err_DoArchive
  Dim ws As DAO.Workspace   'Current workspace (for transaction).
  Dim db As DAO.Database    'Inside the transaction.
  Dim bInTrans As Boolean   'Flag that transaction is active.
  Dim strSql As String      'Action query statements.
  Dim strMsg As String      'MsgBox message.

  'Step 1: Initialize database object inside a transaction.
  Set ws = DBEngine(0)
  ws.BeginTrans
  bInTrans = True
  Set db = ws(0)

  'Step 2: Execute the append.
  strSql = "INSERT INTO MyArchiveTable ( MyField, AnotherField, Field3 ) " & _
    "IN ""C:\My Documents\MyArchive.mdb"" " & _
    "SELECT SomeField, Field2, Field3 FROM MyTable WHERE (MyYesNoField = True);"
  db.Execute strSql, dbFailOnError

  'Step 3: Execute the delete.
  strSql = "DELETE FROM MyTable WHERE (MyYesNoField = True);"
  db.Execute strSql, dbFailOnError

  'Step 4: Get user confirmation to commit the change.
  strMsg = "Archive " & db.RecordsAffected & " record(s)?"
  If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
    ws.CommitTrans
    bInTrans = False
  End If

Exit_DoArchive:
  'Step 5: Clean up
  On Error Resume Next
  Set db = Nothing
  If bInTrans Then   'Rollback if the transaction is active.
    ws.Rollback
  End If
  Set ws = Nothing
Exit Sub

Err_DoArchive:
  MsgBox Err.Description, vbExclamation, "Archiving failed: Error " & Err.number
  Resume Exit_DoArchive
End Sub