N
Neil
I'm using Access 2000 with a SQL 7 back end. I recently implemented some
code in a form's AfterUpdate event which calls a stored procedure which
copies the contents of the current record to a history table. The code works
fine when the user edits and saves the record. However, if the user performs
a Find and Replace, the code hangs.
At first I thought the code was hanging because of multiple records being
replaced. But when I debugged it, I found that it was hanging on the first
record being replaced by the Find and Replace. And whereas the code to copy
the data to the history table only takes about a second normally to execute,
when called during the Find and Replace, it times out with a 60 second
timeout limit.
The stored procedure copies the data entirely in the back end. So it's not
as though Access is trying to do two things at once. But, apparently, the
Find and Replace has a lock on the record, preventing the stored procedure
from executing even a copy on the record.
So, I need to find a workaround. Here are some possibilities.
1) Find a solution to this situation, allowing the stored procedure to copy
the record to the history table during the Find and Replace (not really
expecting to be able to do that).
2) Disable Find and Replace on the form (can't really do that since the
users need Find; and if they can access the Find dialog box, then they can
access Replace, since the two are combined).
3) Add something to code to not call the stored procedure when the
AfterUpdate event is triggered by Find and Replace.
4) Other.
Any ideas/suggestions appreciated.
Thanks,
Neil
code in a form's AfterUpdate event which calls a stored procedure which
copies the contents of the current record to a history table. The code works
fine when the user edits and saves the record. However, if the user performs
a Find and Replace, the code hangs.
At first I thought the code was hanging because of multiple records being
replaced. But when I debugged it, I found that it was hanging on the first
record being replaced by the Find and Replace. And whereas the code to copy
the data to the history table only takes about a second normally to execute,
when called during the Find and Replace, it times out with a 60 second
timeout limit.
The stored procedure copies the data entirely in the back end. So it's not
as though Access is trying to do two things at once. But, apparently, the
Find and Replace has a lock on the record, preventing the stored procedure
from executing even a copy on the record.
So, I need to find a workaround. Here are some possibilities.
1) Find a solution to this situation, allowing the stored procedure to copy
the record to the history table during the Find and Replace (not really
expecting to be able to do that).
2) Disable Find and Replace on the form (can't really do that since the
users need Find; and if they can access the Find dialog box, then they can
access Replace, since the two are combined).
3) Add something to code to not call the stored procedure when the
AfterUpdate event is triggered by Find and Replace.
4) Other.
Any ideas/suggestions appreciated.
Thanks,
Neil