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.

 

[Pat Hartman]

I know it is much more "efficient" to store a bit than four bytes but, it is much less trouble to just use a Small Integer. That was my solution when I ran into this.

That gives you the added advantage of being able to support a null value if you want to.

 

[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

 

[Pat Hartman]

The problem is that Access is OK with having a null value in a Yes/No field but SQL Server is not. When I use Yes/No fields I always set a default of Yes or No but there are cases where I actually want the Yes/No field to be null UNTIL the user makes a decision and that is why I ultimately changed to using small integer instead of Yes/No and just stuck with it. That way, I don't have to think about anything or worry if I am developing in ACE will I have a problem if the client wants to upsize. When you've lost as many grey cells as I have, you need to reduce the amount of decision making that needs to be done for each new app to the absolute minimum

 

[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

 

[Pat Hartman]

Sorry. Access doesn't have a bit data type. I was looking at Byte which does allow null.

Just FYI, Access doesn't complain if you set a YN field to null. It pretends you meant False I guess. I'm not sure it always worked this way.

 

[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.

 

[Pat Hartman]

When you allow null, I would stick with a small integer and 1 for true and 0 for false rather than switch to a string.

 

[RogerCooper]

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

 

[Pat Hartman]

If the field is Y/N AND you want a default, that is good advice. You can default to true or false whatever makes sense. However, if you simply make the default null and the field required, neither ACE nor SQL Server will allow the record to get saved. Therefore, you REQUIRE the user to make a choice of yes or no. It really depends on your business rules.