Provided by Allen Browne, January 2002.
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.
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 procedure below consists of these steps:
Watch out for these serious traps when working with transactions:
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
Home | Index of tips | Top |