Problem with Bit field in SQL Server

[RogerCooper]

I recently added a bit field to an SQL Server table I link to in Access. After doing that, whenever I attempt to edit a record in the Access linked table (even for a different field), I receive the error "This record has been changed by another user since you starting edit it ...". If I create a Select query, I can edit through the query without difficulty.

Any comments on this odd behavior.

 

[theDBguy]

Not sure if this will help, but make sure the bit field has a default value assigned in SQL Server.

 

[RogerCooper]

I will do that and check again.

 

[isladogs]

If you do decide to continue with a SQL Server bit field, ensure that existing values are NOT null as well as setting a default value.
See my article which explains the reasons for the write conflict error

Write Conflict Errors

This article explores some of the causes of the 'dreaded' write conflict error and how to deal with those issues.

www.isladogs.co.uk

I agree with Pat that using a small integer may be less trouble but if you follow the above advice, the bit field won't give you any further trouble

 

[isladogs]

Pat
You wrote that wrongly. Just to correct the above comment.
Bit fields in SQL Server do allow nulls. Access does not
Access doesn't know how to handle any null bit values in linked tables, so a write conflict errors

 

[isladogs]

Boolean fields in Access have always been Yes/No only and occupy 1 byte.

Until now, I'd don't think I'd ever tried using an update query to set them to Null but as you say, the update 'works' without error.
In reality, the values are being set to False

 

[RogerCooper]

Thank you for your help. I will make sure that the fields in SQL Server do not have nulls and default to false. If I am using yes/no in Access field or bit field in SQL Server it is because I don't want null to be an option.

If I want to null to be an option, I follow the practice of my ERP and use length 1 text field with "Y" or "N".

I don't need to use an update query to work around this problem. A select query works, as long as it is not an all columns select.

 

[RogerCooper]

I discovered that it a good idea to prohibit nulls explicitly in SQL server, as well as setting a default of 0.