IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, firstname.lastname@example.org, 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|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Archive: Move records to another table'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...