We help IT Professionals succeed at work.

Can't update MS SQL2005 table from MS Access form

I'm trying to update a field in an MS SQL 2005 table from a form in MS Access.  I keep getting a Write Conflict but I'm the only one using the db.  I get the same error even if I try to update a record in the table without going through the form. I'm the db admin for the database.  I can append with no problem.  The table has a primary key.  Any ideas?
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
If you have bit  fields in your table try setting allow nulls to false

Author

Commented:
You may be onto something but when I attempt to change the data type not allow nulls, I get the following error message:


'Rebates' table
- Unable to modify table.  
Cannot insert the value NULL into column 'Prep01', table 'dbSalesInfo.dbo.Tmp_Rebates'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Is this because I have nulls  and if so how can I get rid of them?

Commented:
Can you initialize the values to spaces if char or 0 if numeric?
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Set the default value of the field to False and if you have any existing NULLS in your bit column, make a backup and run an update query to set them to false:

UPDATE YourTable
SET YourField = False
WHERE YourField IS NULL


(Allowing nulls in bit fields is one consistent cause of that WriteConflict error that I have seen in upsized Access databases.)

Author

Commented:
Thank you!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.