Microsoft Access 2010 Macro Issue --

Without seeing the queries or the macro, ...this is difficult to troubleshoot.

What I will say is that a lot of these "Set and forget" systems, ...typically will require a lot of validation and error handling to run smoothly "unattended".

The full process is about 35 steps many of them iterative

This is a lot of steps to simply run and cross your fingers that everything will work without a hitch.
;-)
For example:
Suppose step 3 runs but does not clear out the table...
Do you have a system in-place to verify the table is cleared?
If not, then step 4 might simply append the records...

What if all the records do not match your criteria, ...then what? (how are you alerted to this?)
Suppose an update fails?
Suppose a calculation produces an invalid result?
for example:
57 multiplied by a  Null Value
Or
A number divided by zero.
Or
A result being defined as a integer, ...but a division calculation produces a double (number requiring a decimal) result.
Then what?
Again, ...how would you be alerted of anything going wrong at any step along the line?
(remember that with a "Full Steam Ahead" macro, ...errors may be cumulative at each step in the process.)
How would you know what errors occurred at which step in the process?

when I run the query's manually step by step the process works great,

When you run the queries manually, ...Access has time to "settle down"
(commit changes, clear buffers, ...release memory, ...etc)
When you do things in "batch mode" (a 35 step macro) , ...timing issue may creep in.

What else is this system doing besides deleting and appending?

Have you considered using SQL action queries in code, instead of running saved queries?
Something "Roughly" like this:

Dim dbs as DAO.Database
Dim strSQL1 as String
Dim strSQL2 as string
'...ect

set dbs=CurrentDB()

strSQL1= "DELETE * FROM YourTable
strSQL2= "INSERT INTO YourTable VALUES(v1,v2,v3,)" 
'...etc

dbs.execute strSQL1,dbfailOnError
dbs.execute strSQL2,dbfailOnError
'...etc

Select allOpen in new window

Again, ...without seeing the macro(s) or queries, ...this is tricky to nail down.

Finally, ...Why so much processing?
Why not just simply append records, and then filter for the current appended records?
(At least then you will have a "History")
For example:
Append records for 4/3/2016
Then filter for 4/3/2016
Then do whatever you need to do with the 4/3/2016 data.
...Then run the append again the next day for 4/4/2016
...repeat daily
With a system like this, you could archive a moths worth of data, ...*Then* clear the table...

But let's see what others may post, ...in case i am not understanding something.

JeffCoachman

Jeff,

   Thanks for the reply,  The overall process is 35 steps, the Macro is only the last 10 that are not iterative.   The first twenty five is a series of imports from CSV, edits and validity checks to fill in certain holes.

   If this were a perfect would the data would all be directly accessible via FDW wrappers, or ODBC links, but it's not so I have to live with accumulating all the base data, via export / import and then verifying that all the relationships are correct and that I don't need to make entries to any of the translation / linking tables.  (Always have to make at least some, and then rerun the checks.).

    Once all this pre-work is done I can run the process with builds a new single table which is the basis for an EDI 850 invoice that gets submitted to a billing system.   So the steps set the base billing rate, the options rates if  corresponding rows exist in the proper options table, the tax rate, and finally sum it all up into the row total.  

    Since it has to be done in Access (Customer is always right) I have a total of 10 steps to go through once all the data is collected from the various data sources and analyzed.

    If I move the existing Steps from a query's to a single "SQL action queries" I loose the ability to work through the process manually and verify each step when a change has to happen,  

    An yes, I suspect I do need a pause or something, just not sure how to code it, does an SQL sleep() work here?

    TIA..

No other comments yet ..  Going to see if I can put a little function together  for 'DoEvents.

Had to add some time between the steps --