Sudden Onset of Write Conflict

  • Thread starter Thread starter Dawn Crosier
  • Start date Start date
D

Dawn Crosier

I have an ADP which has been in production for sometime now. Within the
last month users are getting an error code Error 7787 Response 1 which is a
Write Conflict error from SQL Server. This happens when they attempt to add
an additional record to a joining table. The joining table is a dual
primary key table with varchar 100 and varchar 50 fields. (One of each).

The troubling portion of this problem, is that if the user closes down the
main form and re-opens it. The record has been added / committed.

I have other joining tables which are configured exactly the same and they
are working with no problems. My ADP has a main form with subforms located
on Tabs. I have 3 subforms setup exactly the same and two of them are NOT
experiencing problems YET. The joining table which is causing problems has
773 records in it while the other two tables contain 138 and 132 records
respectively.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.
 
I've been problem-solving on this, and have found two things which seem to be
factors.

First, when the recordset in question contained an ntext field, the 7787
error occurred; when I switched the data-type for that field to be an
nvarchar, everything worked as expected.

On a second form, which is functionally identical to the form I was having
problems with, but links to/from a different tables, I noticed it worked
fine. The only difference I could see is that on the form that didn't work,
I was linking an underlying value on the child form to a text box on the
parent; in the form that worked, I was linking underlying fields for both.
So make sure your linked fields aren't referring to form elements. (In other
words, rename all your controls so that there's no possibility of
overlap...RespondentID to ctlRespondentID, etc.)


Hope this works for you, as it did for me!
 
Dual primary key with varchar 100 and 50? Strange design.

The most probable cause for the error 7787 is a simultaneous edition of the
same record by two different users at the same time. However, this cannot
be the source of your problem because you are adding new records (excerpt in
the improbable case where your applicatin will changes or edit these primay
key values for already entered records).

You should trap the error 7787 and repeat the operation a few times until it
succeeds.

S. L.
 
Oh, another thing that worked that I forgot to mention was to leave the ntext
field blank when creating the record, and only update it on editing. This
would probably have to be done programatically in most cases, and you'd
probably want to requery the recordset...I didn't play with it extensively.

If you've got a high user-tolerance, though, you might just be able to tell
them not to fill in the text field, but only to fill in something else (if
you have something else, and assuming that this is even the same problem!),
then come back to the record later. Not something I'd terribly recommend,
but it may work well enough depending on the specifics of your database.



Rob
 
An old OLEDB design feature is the requirement that all text, image and
ntext fields should come last in the select statement. I don't know if this
requirement is still valid but you may try setting this ntext field at the
end of your query statement.

BTW, what version of Access are you using and are you playing with the
RecordsetClone method somewhere in your code?

S. L.
 
What I eventually did to solve the problem was to split my module into two.
There is a limit to the size of a module. Once I did that, the problem was
solved.

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.
 
Maybe this was some sort of corruption problem. If you have time, you may
try to reunite them again and see what's happen.

S. L.
 
Hi Sylvain,

Sorry for the delay in getting back to you, but the web-based newsgroups on
Microsoft's web site don't seem to be working at the moment...I finally gave
up and did it "the old-fashioned way" (aka a newsreader) just to get at the
replies I knew were waiting for me.

Anyway, to answer your question, I'm using Access XP. And no, neither the
subforms, nor the parent forms are using the RecordsetClone method anywhere.
Being sub-forms, though, who knows what MS is doing behind-the-scenes? :)

I wasn't aware of the OLEDB ntext/image requirement, though, so that may
have helped. In any event, it's now an nvarchar field (which is all it
needs to be...the ntext was a leftover of the Access to SQL Server 2000
Upsizing Wizard that I hadn't noticed until it started causing headaches),
so everything works fine now. I'll keep your comments in mind, though...in
the event that I come across a similar problem in future, I'll try playing
around with the underlying queries or making the form unbound and updating
the "underlying" recordset by-hand and see if it works.



Thanks for the suggestions!
Rob
 
Back
Top