By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,639 Members | 2,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

Archiving data

rcollins
100+
P: 234
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
May 15 '07 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,638
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
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. DoCmd.CopyObject , "tblCientContactArchive", acTable, "tblClientContact"
  4.  
  5. 'DELETE ALL Records from tblClientContact
  6. DoCmd.SetWarnings False     'don't feel like seeing Deletion Prompts!
  7.   strSQL = "DELETE * FROM tblClientContact;"
  8.   DoCmd.RunSQL strSQL
  9. DoCmd.SetWarnings True
May 15 '07 #2

rcollins
100+
P: 234
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.
May 17 '07 #3

ADezii
Expert 5K+
P: 8,638
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!
May 17 '07 #4

rcollins
100+
P: 234
Did we ever find an answer to this?
May 24 '07 #5

ADezii
Expert 5K+
P: 8,638
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).
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. DoCmd.CopyObject , "tblCientContactArchive", acTable, "tblClientContact"
  4.  
  5. 'DELETE ALL Records from tblClientContact
  6. DoCmd.SetWarnings False     'don't feel like seeing Deletion Prompts!
  7.   strSQL = "DELETE * FROM tblClientContact;"
  8.   DoCmd.RunSQL strSQL
  9. DoCmd.SetWarnings True
  10.  
  11. RefreshDatabaseWindow              'Add this line of code
May 24 '07 #6

rcollins
100+
P: 234
So, n o good. I can't figure out why it would make the archive table linked to the main table
May 25 '07 #7

rcollins
100+
P: 234
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command30_Click()
  2.     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
  3.         DoCmd.SetWarnings False
  4.  
  5.         'put new records in archive table
  6.         DoCmd.OpenQuery "qryAppentClientContactArchive"
  7.  
  8.         'deletes records from original table
  9.         DoCmd.OpenQuery "deleterecordsforarchive"
  10.  
  11.         DoCmd.SetWarnings True
  12.         MsgBox "All records have been archived.", vbInformation
  13.     End If
  14. End Sub
May 25 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.