What I want to have is the error trapping so that if any errors occured while inserting the table, I want to stop inserting, delete the things that were inserted and start the inserting all over again.
How are you doing the insert? I am inferring that we are talking about VBA as part of this, because that is the only way to catch a trap signal. But is the actual insert done by opening an action query with a DoCmd.OpenQuery... or by VBA recordset operations involving ".AddNew" and ".Update" operations?
The query won't stop inserting but will flag errors. On the other hand, the very first error will stop the .Update.
So you have an "OnError Goto Whoops" statement. Have it check the Err object to find the code number. (Or maybe you don't care - just want ANY error to stop the process.)
If that is true, then in your VBA code you have two cases.
...
OnError Goto Whoops
{loop to insert records or DoCmd.OpenQuery to append records}
{code to commit appended records}
Goto We_Did_It
Whoops:
{tests on whether this qualifies as a stopper}
If {stopper-test} Then
Resume We_Goofed
Else
Resume Next
End If
We_Goofed:
{code to roll back the inserts}
Goto We_Did_It
{etc etc etc}
As to how you would impelment this, I would take the approach of including a Yes/No flag that says "Commit" - always stored with default value of "No" (Or see my ending comments as an alternative if certain conditions apply.)
If you have to back out all of the recent inserts, have an Erase query in the wings that you can run when you need it (DoCmd.OpenQuery works for action queries, too), where the criterion for erasure is "Commit=No". Have another query waiting in the wings that does an update. If the whole pile works, update the commit flags to Yes. But don't run the COMMIT query until you are ready.
Now, if this were an SQL transaction to an ODBC server, you could use the transaction concept. Declare the beginning of the transaction, start doing it, and if nothing happens, do a Commit. But from the way you wrote the question, I'm assuming (possibly incorrectly) that this latter solution isn't necessarily what you wanted. Just to be safe, look up the stuff on transactions anyway. It might help you. And I would be remiss if I did not at least mention the .Commit / .Rollback concept, which Access DOES support in at least SOME circumstances.
