loop of DELETE sql on one record - occasional error 3086 (1 Viewer)

Hi,
in brief: running these simple lines of code
sometimes causes error 3086 - 'Could not delete from specified tables'. usually, works fine.

In detail:
In a data update task: some 50 XML files containing some 30K records for updating data: delete some, add some, replace most (=delete current, and insert new).

The process:
for each file, for each record in the file, according to Primary Key:
1. make an attempt to Delete the record/s:
2. if the adjustment type is not 'delete', insert the record into the data.

Error handler registers in tblErrors PK, file name, and Err.Number, if occurs.
At the end of the process I first had some 5K Error registrations, with the above error, other 20K~ records get deleted fine.

the process is about 12-15 minutes long.
I am working in Acc2010 on a 2003 .mdb file, in Win 7. My client will run it in Acc2003 on win7.

Any ideas why would this error occur occasionally?

Would removing indexes make a difference?

Thanks In advance!

So our magic eyesight can see all the code you're running and identifies line 42 as the likely culprit.


If you want a better bet, you might consider providing something solid to look at.

Hi spike, and thanks for replying

Well, if I had something solid I would have posted it... Maybe a few facts:
1. The Process was tested on this same data a few times, each test resulted with a different number of errors, not necessarily from the same records (same p_Code and same XmlFile).
2. I ran a debug mode on 5-6 p_Codes in the error-log to pin-Point the error, and no error occurd...
3. The process executes about 4 SQLs, INSERTs and DELETEs, for each of 31K adjustment records, Total of over 125k SQLs.
4. These tables have indexes on them, Primary key, and others, more overhead on each SQL.
5. It seems the errors occur on orders like "CurrentDB.Execute", in other words: in Jet, not in VBA.

My guesses: too much overhead in the process causes these errors.
If true, I could think of 2 direction of a solution:
1. Remove Indexes before the process, and recreate them afterwords.
2. Save the XML data to a table, and then Delete and Insert sets to Packs and Components tables. This solution calls for significant coed modification.

My questions:
Could my guess a reasonable explanation for this behavior?
Would these directions solve the problem?

Thanks in advance!

Hope I'm over with this:

I believe the issue is DB locks, not index overhead.

First: when error handler had code:
Code execution usually continued.

While process ran, I saw the ldb file being created and deleted constantly, locking and unlocking the tables. When data was Deleted before the file-system could remove the locking file, the error accord.

I rebuilt the process, to insert/delete sets of data, have now less locks and unlocks, it now works fine.