Archiving data

[rcollins]

I'm back, thanks for the help with the import, our first import went with minor clitches, mostly with the spreadsheets. On to the next stage. I need to archive the main table of my database. Basically, I just want all of the info to move to fromtblClientContact to tblClientContactArchive. Please advise

[ADezii]

I'm back, thanks for the help with the import, our first import went with minor clitches, mostly with the spreadsheets. On to the next stage. I need to archive the main table of my database. Basically, I just want all of the info to move to fromtblClientContact to tblClientContactArchive. Please advise

Dim strSQL As String
 
DoCmd.CopyObject , "tblCientContactArchive", acTable, "tblClientContact"
 
'DELETE ALL Records from tblClientContact
DoCmd.SetWarnings False     'don't feel like seeing Deletion Prompts!
  strSQL = "DELETE * FROM tblClientContact;"
  DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[rcollins]

So, real screwy behavior. When I run this code, it copies over, then deletes from the original table. As soon as it deletes from the original, then all of the data in the archive is gone also. If this isn't bad enough, when I paste the info back in my original table, the data in the archive comes back. I looked in the link table manager to find that there is a link from the archive table to the original table. How can I use a table that is already created. If I make an archive table, it prompts to replace the existing one, then links the new one with the original table.

[ADezii]

So, real screwy behavior. When I run this code, it copies over, then deletes from the original table. As soon as it deletes from the original, then all of the data in the archive is gone also. If this isn't bad enough, when I paste the info back in my original table, the data in the archive comes back. I looked in the link table manager to find that there is a link from the archive table to the original table. How can I use a table that is already created. If I make an archive table, it prompts to replace the existing one, then links the new one with the original table.

You're right, this is real screwy behavior - will have to think on this one!

[rcollins]

Did we ever find an answer to this?

[ADezii]

Did we ever find an answer to this?

I'm sorry but i've repeated this process on my PC over and over and over again, and had no problems at all. I can't imagine what would be causing this behavior. Just as a long shot, add a single line of code at the end (RefreshDatabaseWindow).

Dim strSQL As String
 
DoCmd.CopyObject , "tblCientContactArchive", acTable, "tblClientContact"
 
'DELETE ALL Records from tblClientContact
DoCmd.SetWarnings False     'don't feel like seeing Deletion Prompts!
  strSQL = "DELETE * FROM tblClientContact;"
  DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
RefreshDatabaseWindow              'Add this line of code

[rcollins]

So, n o good. I can't figure out why it would make the archive table linked to the main table

[rcollins]

How about this.. I had to make 2 more querys, one to append to the archive table and one to delete the records from the original table. This worked great.

Private Sub Command30_Click()
    If MsgBox("Are you sure you want to do this? This process will move all of the records for the past year to an 'archive' table!", vbYesNo) = vbYes Then
        DoCmd.SetWarnings False
 
        'put new records in archive table
        DoCmd.OpenQuery "qryAppentClientContactArchive"
 
        'deletes records from original table
        DoCmd.OpenQuery "deleterecordsforarchive"
 
        DoCmd.SetWarnings True
        MsgBox "All records have been archived.", vbInformation
    End If
End Sub