Still Executing Problem???

[Bill]

I have two processes that follow each other and update
the same table.

Process 1 uses a recordset. After looping through each
record of the recordset, updating the contents, and
issuing the update method, I issue the close method and
set the recordset to nothing.

Process 2 is querydef that updates the same table.

However, when process 2 starts it encounters a lock on
the table which causes a 3218 error to be thrown. When I
step through the code, I do not get this error. This
tells it is a timing issuer. Eventhough Access has
returned control to the code so that it can proceed,
Access is still executing the updates from the recordset.

When I step through the code, I am going slow enough this
synch issue does not appear.

By inserting a loop that counts from 0 to 100000
introduces a delay that is enough to work in this case.
However, there is no way for me to determine what
is "enough" ahead of time. So this is not a good
solution.

The stillexecuting property of the recordset does not
work in this particular case.

Does anyone have a solution for this?

Bill

 

[Dirk Goldgar]

I have two processes that follow each other and update
the same table.

Process 1 uses a recordset. After looping through each
record of the recordset, updating the contents, and
issuing the update method, I issue the close method and
set the recordset to nothing.

Process 2 is querydef that updates the same table.

However, when process 2 starts it encounters a lock on
the table which causes a 3218 error to be thrown. When I
step through the code, I do not get this error. This
tells it is a timing issuer. Eventhough Access has
returned control to the code so that it can proceed,
Access is still executing the updates from the recordset.

When I step through the code, I am going slow enough this
synch issue does not appear.

By inserting a loop that counts from 0 to 100000
introduces a delay that is enough to work in this case.
However, there is no way for me to determine what
is "enough" ahead of time. So this is not a good
solution.

The stillexecuting property of the recordset does not
work in this particular case.

Does anyone have a solution for this?

I don't know. You might try inserting the statement

DBEngine.Idle dbRefreshCache

between the processes.

 

[Albert]

If you know the error code returned by JET is 3218. I think you can use
Resume statement in your error handling routine. It's also a good practice
to preceding Resume with DoEvents statement. For more practical, You may
count number of time Resume is executed. If exceed than number you specify,
Raise error message.

HTH

 

[Bill]

The above two suggestions I will try. Also by converting
Process one to a query seems to have solved the problem.

Bill