Archiving records from two tables

[smith844]

I have a database for tracking tasks. each task may have associated documents for it which are kept in a separate table.

I have an archiving procedure which takes closed or completed tasks (from the main table) and appends them to an archive table but it leaves the associated documents in the second table, or if I enforce integrity it will delete them.

Anyone have a suggestion on how i can archive records from two tables at the same time I include the archive code for your information....

Many Thanks as always for your help and assistance

 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' procedure archives closed or complete records to archive table and then         '
' Deletes original records from source table                                      '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

On Error GoTo Err_btnArchiveAdhoc_Click

    Dim strMySql As String, strResponse As String, intRecordCount As Integer
    
    Dim cnn As ADODB.Connection
    Dim rstTemp As ADODB.Recordset
 
 strResponse = MsgBox("By clicking Yes you will Archive any closed or" & _
 vbCrLf & "completed tasks and delete the original records from the table", vbExclamation + vbYesNo, gstrAppTitle)
        If strResponse = vbNo Then
        Exit Sub
        Else
 Set cnn = CurrentProject.Connection
 Set rstTemp = New ADODB.Recordset
 
' builds sql string to append closed or complete records from the original table into archive table
    strMySql = "INSERT INTO tblArchiveAdHocTasks ( Seq_Number, Item_Type, TaskTitle, Task_Description, TaskActions, StartDate, TgtDate, ReviewDate, TaskLead, Status, LastUpdatedBy, estHours, Progress, PersIDAssocPerson1, PersIDAssocPerson2, PersIDAssocPerson3, PersIDAssocPerson4, PersIDAssocPerson5 )"
    strMySql = strMySql + " SELECT tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
    strMySql = strMySql + " FROM tblAdHocTask "
    strMySql = strMySql + " WHERE (((tblAdHocTask.Status)=6 Or (tblAdHocTask.Status)=10))" ' status 6 is closed and 10 is complete
    DoCmd.SetWarnings False
    
    'DoCmd.RunSQL strMySql ' runs append query
rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic


    DoCmd.SetWarnings True ' turn warnings back on
        ' deletes records that have been archived above
        strMySql = "DELETE tblAdHocTask.Seq_Number, tblAdHocTask.Item_Type, tblAdHocTask.TaskTitle, tblAdHocTask.Task_Description, tblAdHocTask.TaskActions, tblAdHocTask.StartDate, tblAdHocTask.TgtDate, tblAdHocTask.ReviewDate, tblAdHocTask.TaskLead, tblAdHocTask.Status, tblAdHocTask.LastUpdatedBy, tblAdHocTask.estHours, tblAdHocTask.Progress, tblAdHocTask.PersIDAssocPerson1, tblAdHocTask.PersIDAssocPerson2, tblAdHocTask.PersIDAssocPerson3, tblAdHocTask.PersIDAssocPerson4, tblAdHocTask.PersIDAssocPerson5"
        strMySql = strMySql + " FROM tblAdHocTask"
        strMySql = strMySql + " WHERE (((tblAdHocTask.Status) = 6 Or (tblAdHocTask.Status) = 10))" ' status 6 is closed status 10 is complete
        rstTemp.CursorLocation = adUseClient
rstTemp.Open strMySql, cnn, adOpenDynamic, adLockOptimistic
        
    End If
MsgBox "Export Complete ", vbInformation, gstrAppTitle
Exit_btnArchiveAdhoc_Click:
    Exit Sub

Err_btnArchiveAdhoc_Click:
    If Err.Number = 2501 Then ' runtime error as user cancelled delete part of query
    Exit Sub
    End If
    
    MsgBox "'Error" & Err.Number & "'" & vbCrLf & Err.Description
    
    Resume Exit_btnArchiveAdhoc_Click
    
End Sub

 

[ColinEssex]

Why do you need to archive the data?

If its got a closed date then it won't be included as current (or open) anyway

Col

 

[gemma-the-husky]

to do it your way you and maintain ref. integrity you need to

1. copy headers to archive (ie master table 1)
2. copy details to archive
3. delete details from live
4. delete headers form live

then you can have ref integrity in both the master and the details fiels.

i do this in some cases - i also maintain before and after counts to make sure the move succeeded, and wont do the deletes until the moves are checked and verified, otherwise you can lose data!

 

[smith844]

thinking out loud

Colin
We could generate a large number of records (tasks) so I felt it was best to keep the overheads to a minimum to get them out of the main table.

I was thinking something along the lines (and this is conceptual as i have no idea how to do it!!!) of

Writing some code to:
1. Look into the archive table immediately that it is updated, or use the fact that the records in the main table are closed or complete.
2. Check the ID (task number) and then compare this (somehow) to the second table and copy this second (related) record to another archive table..

Could I extract (archive) the records one at a time and therefore have the unique ID to use on the second table I wonder......

or maybe use a loop of some sort to get the unique ID and use it to copy both table entries.......

answers on a postcard please, pretty please in fact.....

David

 

[ColinEssex]

I've had tables with over a million records working just fine, how many will you have?

Col

 

[smith844]

about 3. But then its more about the speed of the network I am running it on, well when I say running its more of a jog

 

[smith844]

I had a few thoughts of my own and now it works OK

I used the following code

strMySql = "INSERT INTO tblArchiveAdHocAssocDocuments ( DocID, TaskNo, DocumentName, DocumentDescription, DocumentLocation )"
    strMySql = strMySql + " SELECT tblAdHocAssocDocuments.DocID, tblAdHocAssocDocuments.TaskNo, tblAdHocAssocDocuments.DocumentName, tblAdHocAssocDocuments.DocumentDescription, tblAdHocAssocDocuments.DocumentLocation"
    strMySql = strMySql + " FROM tblAdHocAssocDocuments, tblArchiveAdHocTasks "
    strMySql = strMySql + " WHERE tblAdHocAssocDocuments.TaskNo = tblArchiveAdHocTasks.Seq_Number"

This looks at the archive table, immediately after the code in post 1 has appended records and then appends any associated documents records from table 2 to another archive table.
Then when the code deletes the original records as referential integrity is enforced it deletes all the associated documents, cleaning up the tables, nicely

What a great way to spend the afternoon.....

Dave