I manage a database that gets daily updates with new data. Some tables get new records added since the last update while others get wiped completely before adding all data (this is because the elements of the second dataset may change frequently). I do the updates by first capturing the new data into temporary tables by pasting from Excel, then I open a form that has an "On Timer" event to run some VBA that shuttles the data around, ultimately wiping the temporary tables back to their original blank state. In the middle I stop to review the new data to ensure as best possible there are no entry errors from people who insist on ignoring the rules.
(Note that the MS Access database is only meant to provide additional functionality. The actual source data is coming from Amdocs and transported to Excel via Business Objects - An obvious kludge, but I am at the mercy of our IT groups with regard to what I can do in Amdocs and when it can be done while I am in full control of what/when with regard to the Access data).
The problem being, that on rare occasions, there will be an error while updating. With error testing off to avoid many repetitive mouse clicks, the whole task starts over. If this happens after one or more of the wrong tables is wiped, I lose all the data. My Band-Aid for this is to exit Access temporarily before executing the final update macros to make a copy of the database that saves my place. Then I reopen and finish from where I left off.
However, it seems to me a more elegant solution would be to have update queries that check to see if null rows are returned prior to attempting to update. e.g. if the table that should have rows of temporary data does not, then skip deleting everything in the target table, otherwise proceed normally.
The VBA in this is all at the elementary level. Here is a sample of the sequence at the end:
Option Compare Database
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)
Private Sub Form_Timer()
'------------------------------------------------------------
' PostFixed-NewData
'------------------------------------------------------------
' Turn off messages to avoid mouse click responses to running sequential maintenance queries
DoCmd.SetWarnings (0)
' Append all new cases to the standard Case table
DoCmd.OpenQuery "Append New Cases", acViewNormal, acEdit
' Delete all previously existing details
DoCmd.OpenQuery "Delete Old Details", acViewNormal, acEdit
' Append all new details to the standard Detail table
DoCmd.OpenQuery "Append New Details", acViewNormal, acEdit
' Append new case notes to the standard Case Notes table
DoCmd.OpenQuery "Append New Case Notes", acViewNormal, acEdit
' Remove all case records from the cases holding area
DoCmd.OpenQuery "Delete Updated Cases", acViewNormal, acEdit
' Remove all detail records from the details holding area
DoCmd.OpenQuery "Delete Updated Details", acViewNormal, acEdit
' Remove all case note records from the case notes holding area
DoCmd.OpenQuery "Delete Updated Case Notes", acViewNormal, acEdit
' Turn messages back on to ensure getting appropriate warnings or other information in response to actions
DoCmd.SetWarnings (-1)
DoCmd.Close
End Sub