access數(shù)據(jù)存檔-將記錄移到另一個(gè)數(shù)據(jù)庫中

在不需要的舊數(shù)據(jù)情況下，例如數(shù)十萬條記錄，并且不斷添加新記錄。我們會(huì)把舊的不需要數(shù)據(jù)單獨(dú)的保存在另外的數(shù)據(jù)庫中。

執(zhí)行過程包括以下步驟：
1. 開始一個(gè)事務(wù)。
2. 執(zhí)行追加查詢。
3. 執(zhí)行delete查詢。
4. 獲取用戶確認(rèn)以提交更改。
5. 如果任何步驟出現(xiàn)錯(cuò)誤，則回滾事務(wù)。


詳細(xì)代碼：

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  MyTable(Field1, Field2, Field3 ) " & "IN ""C:\MyData.accdb"" " & "SELECT  到貨日期, 采購編碼, 到達(dá)數(shù)量 FROM tblPurchaseDetail WHERE (MyYesNoField = True) "
    db.Execute strSql, dbFailOnError
    
'    Step 3: Execute the delete.
    strSql = "DELETE FROM tblPurchaseDetail 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

把數(shù)據(jù)庫的舊數(shù)據(jù)添加到C:\Mydata數(shù)據(jù)庫中