L
LarryP
I'm building a database that includes a procedure to "promote" selected
records through the various phases of a project. It first builds a recordset
of only the selected records, then churns through them one at a time. For
each item it runs a simple query to add a completion date for the phase it
was in, after which it runs a second simple query to create a new record for
the phase it is moving into. In some but not all cases, if the record has
bypassed any intervening phases it runs a third query to put a comment in the
newly created phase record reflecting the bypass.
Speed is a problem, any sidebar thoughts on ways this could run faster are
welcome, but the REAL stumper for me just now is, if the procedure is in the
middle of promoting a large number of records and one switches over to EMail
or some other application, on returning the Access database sometimes appears
to lock up tight and quit doing anything. The user then can't even go back
to Email and do something productive, unless he first uses the Task Manager
to bail out of Access. Doing that is of course fraught with data corruption
risks I don't even want to think about.
Anyone have any notion why this might be happening, and how I can code
around it? (Oh, yes: my testing experience is that if you just wait it
out, Access will eventually wake back up and finish the process, but my
customers for this thing are way too impatient for that, especially if in the
meanwhile they can't do anything else either.)
records through the various phases of a project. It first builds a recordset
of only the selected records, then churns through them one at a time. For
each item it runs a simple query to add a completion date for the phase it
was in, after which it runs a second simple query to create a new record for
the phase it is moving into. In some but not all cases, if the record has
bypassed any intervening phases it runs a third query to put a comment in the
newly created phase record reflecting the bypass.
Speed is a problem, any sidebar thoughts on ways this could run faster are
welcome, but the REAL stumper for me just now is, if the procedure is in the
middle of promoting a large number of records and one switches over to EMail
or some other application, on returning the Access database sometimes appears
to lock up tight and quit doing anything. The user then can't even go back
to Email and do something productive, unless he first uses the Task Manager
to bail out of Access. Doing that is of course fraught with data corruption
risks I don't even want to think about.
Anyone have any notion why this might be happening, and how I can code
around it? (Oh, yes: my testing experience is that if you just wait it
out, Access will eventually wake back up and finish the process, but my
customers for this thing are way too impatient for that, especially if in the
meanwhile they can't do anything else either.)