Write Conflict

[Rob]

Hello All,

I am having problem with my database. I have an Access front end
and SQL back end. I have a main form that creates a record with some data
and a subform that allows the user the create multiple records in another
table that coinsides with the main form data. I have a button that allows
the user to add a new record to the subform and when the button is clicked it
takes the primary key data from a field in the main form and copies it to the
same field in the subform to create the link. In SQL I made sure that the
subform had an int field that was incremented to keep from creating a
duplicate (this allows the user to create mutliple records on the subform
that link to the one record on the main form).

I have not created any relationships in Access since I tell the subform to
take the main forms' key field data to create the link. I am getting two
errors:

Invalid Use of Null
Write Conflict
The Microsoft Jet Database stopped the process because you and another user
are attempting to change the same data at the same time

The last one shows up if I try to manually delete the record from the table
in Access.

There is no one else in the database since I am testing it right now. I can
go back to SQL and delete or edit the record in question. Just not through
Access. I am not sure what to do since there is not invalid use of null - I
have determined that the key field data is being copied. The record will
save if I tell the write conflict to drop changes and the key field data is
there from the main forms' data.

Any suggestions would be greatly appreciated.

 

[Paul Shapiro]

The write conflict can occur if SQL Server is doing ANYTHING when the
changes are saved. Is there a trigger on the affected table? If you're using
a Stored Procedure to execute the save, make sure it includes Set NoCount On
at the beginning of the SP or trigger.

Other than that, run the SQL Server Profiler to see exactly what commands
Access is sending to the SQL Server. See SQL Books Online for Profiler info.

 

[Rob]

Paul,

Thank you for the information. I actually found the problem. I noticed
that one of my fields was set to bit (for the yes/no field in access). This
was causing a write access error since it could not store the -1 or 0 in the
bit field. Funny that when you upsize a database from Access to SQL (that is
how I started with this data) it converts a yes/no field to bit instead of
numeric even though it can't store the subsequent data.

Thanks again for the response.

Rob

 

[Sylvain Lafontaine]

Don't know what version of Access do you have but usually, this problem is
solved by using the latest service pack and/or hotfixe. (Applying the
latest hotfixe is important for many version of Access when using either a
MDB file or an ADP project for solving this recurring problem.).

Making sure that the Bit field cannot be null (ie., no triple state value)
and has a default value of either 0 or 1 will also help in solving this
problem. Of course, using a short int instead of a Bit field will also
solve the problem in every case.

Notice that this has been a recurring problem with all versions of Access
(MDB & ADP) and that sometimes, applying a service pack break the system
(instead of repairing it). This is why it's important to always look for
any hotfixe to be applied after applying any SP.