SQL Update Trigger causing error 3197 in Form-AfterUpdate

[Douglas J. Badin]

I am using an .adp with Access 2002 and 2003 running on Windows XP and
having the same problems. The backend is SQL Server 2000 running on Windows
2000..

Here is the simplified version: In Access I have a form that has a
BeforeUpdate and and AfterUpdate form events.

In the AfterUpdate event I add a Bill to QuickBooks via qbXML then I do the
following snippet:

Set rst = Me.RecordsetClone
rst.MoveLast
rst.Bookmark = Me.Form.Bookmark
rst.Edit
rst("QB_TXN_ID").value = objBill.TxnID
rst.Update

2 weeks ago I added an Update Trigger on the table being used in the Form
and everything was working fine.

Mysterously (because I don't know what changed) 1 week ago I started getting
a 3197, "The Microsoft Jet database engine stopped the process because you
and another user are attempting to change the same data at the same time."
on the rst.Edit statement.

I have tried adding the following code before getting the RecordsetClone but
it didn't help:

With Me.Form
If .Dirty Then .Dirty = False
End With

Me.Refresh

Any thoughts?

Thanks,

Doug

 

[Sylvain Lafontaine]

Since WinXP SP2, I don't use Access 2002 anymore because of recurrent
problems with RecordsetClone. However, with A2003, I don't have any
problem.

Second, directly editing the recordset of a bound form has always be an
operation very prone to error. Usually, the best way of doing this is to
use a distinct ADODB object for making the update and then resync or requery
your form.

With the .Dirty property, you should deal with it by saving your record
instead of trying to set to false:

If (Me.Dirty) Then
DoCmd.RunCommand acCmdSaveRecord
End If

Finally, I don't see the logic of doing a "rst.Bookmark = Me.Form.Bookmark"
after a "rst.MoveLast".

S. L.

 

[Douglas J. Badin]

Hi Sylvain,

The acCmdSaveRecord didn't help.

I ended up putting in some error logic to handle the 3197 once and retry the
rst.Edit which seems to be working.

The Logic with the rst.MoveLast before the rst.Bookmark = Me.Form.Bookmark
has to do with a problem I found a while back. It was something like this:

There are multiple Bookmarks, each associated with about 25,000 records.
For tables with more than 25,000 records, to get all the bookmarks to load a
MoveLast is needed. For example, I found that without it, if I tried to
edit the record 25,001, meaning is was using the 2nd bookmark, I was really
editing the record 1 associated with the 1st bookmark. Meaning,
rst.Bookmark = Me.Form.Bookmark was loading the first Bookmark I guess
because it didn't know about the 2nd Bookmark. Anyway, this corrected the
problem.

So, whenever I am dealing with Bookmarks, in best practice, I add the
MoveLast code.

Doug

 

[Sylvain Lafontaine]

Very stranget this thing about bookmarks. Is it possible that you may have
tried to use bookmarks from a previous query of the recorset? Bookmarks are
not guaranteed to persist (remains the same) after a requery.

With what kind of query did you open your recordset?

S. L.

 

[Douglas J. Badin]

Hi Sylvain,

Can't remember that much detail right now. It was 6 months to a year ago.
It did take a while to figure out.

Doug