Problem with SP during F&R

[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

[Tom van Stiphout]

On Mon, 03 Mar 2008 12:30:38 GMT, "Neil" <no****@nospam.netwrote:

Copying to a history table is something you always want to have
happen, even if your VBA code doesn't run, so implement this in an
update trigger in Sql Server.

-Tom.

>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

[Neil]

That solved it. Thanks, Tom. Strange though: you'd think that, since the
copying was done in the back end through a stored procedure, that it
wouldn't matter if it was called from the AfterUpdate event, since Access
wasn't doing any copying anyway. Oh well. Like you said, it's a better
solution this way anyway. Thanks again.

Neil
"Tom van Stiphout" <no*************@cox.netwrote in message
news:a5********************************@4ax.com...

On Mon, 03 Mar 2008 12:30:38 GMT, "Neil" <no****@nospam.netwrote:

Copying to a history table is something you always want to have
happen, even if your VBA code doesn't run, so implement this in an
update trigger in Sql Server.

-Tom.

>>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