Access table Yes/No Data type default value when pushed to SQL (1 Viewer)

[abette]

Hi - I had an Access table that had a field with a data type of yes/no. I had pushed the table to our SQL server and now when the user is updating a record using a form (the record source is the SQL table) the receive a write conflict error message. I search the Internet and found a post that said this can happen when you have a bit data type on a SQL table - hence I now do since pushing the table to SQL the yes/no data type is a bit! What would a valid default value be? 1 or 0 or yes or no?
Thank you!

 

[theDBguy]

I would say either 1 or 0.

bit (Transact-SQL) - SQL Server

bit (Transact-SQL)

docs.microsoft.com

 

[Minty]

I would simply set it integer field and use 0 and -1 - http://allenbrowne.com/NoYesNo.html

 

[gemma-the-husky]

I've had that. It's so irritating. Is this the one where you have to use dbSeeChanges, or is that to do with autonumbers?

 

[Minty]

@gemma-the-husky dbSeeChanges - That's anything that updates a record in a SQL table (in code obviously).

 

[Pat Hartman]

The problem is that the default conversion of Y/N to SQL Server is to the Bit data type which is either true or false. The conflict is that in Access the Y/N data type is actually tri-state in that it allows Y/N/Null

So like the others, I force the conversion to small integer OR I don't use Y/N when I create the app if I know I want to support Null. And that solves future conversion problems. If you are OK with only Y and N, then make the field required and either define a default of true or false whatever makes sense if there is a logical default. If there is no logical default, make the default Null and because you have made the field required, Access will not save the record unless it has a valid non-null value.

 

[isladogs]

Sorry to disagree but that explanation is incorrect.

In SQL Server, bit fields can be true/false or null. In Access, only true or false are allowed.
So if a SQLServer bit field has no default value and is left null for one or more records, Access doesn't know how to interpret those records and any update query based on those records will throw a write conflict error...whether or not those fields are being updated.
The solution, as already stated, is to set a default value for all bit fields (either true or false will work) in SS and ensure all existing null records are updated to the default value.

Within Access, a bound checkbox is either true or false...but an unbound checkbox can be tristate because its not bound to a field

 

[Pat Hartman]

Sorry for the confusion. I switched years ago to not using Y/N fields due to the conflict with SQL Server and so all my checkboxes are bound to integer (Jet/ACE)/Small Int (SQL Server) to avoid the issue.

 

[Isaac]

I agree with "not using Y/N fields". I avoid creative datatypes where possible and go for plain jane stuff. If you need to store a thumbs up or thumbs down, there are numerous regular ways to do it that require zero interpretation and study to understand and use. (Even though the day may come when Access actually says this is a Thumbs Up/Thumbs Down field and we will all scratch our heads and give it a thumbs down.....LOL)

 

[pbaldy]

Is this the one where you have to use dbSeeChanges, or is that to do with autonumbers?

That has to do with identity fields (SQL Server's version of autonumber). It isn't required if the table doesn't have an identity field, but it doesn't hurt either. I include it by default in my template code.

 

[gemma-the-husky]

Yes, I "knew" that wasn't quite right. I had the same issue with yes/no fields though, that changes produced an error of the type listed, but I can't recall the solution. I didn't replace the yes/no fields with integers. It's nice to be have the option of having bound check boxes, and using code that just says

myvar = not myvar

 

[pbaldy]

Another common issue with bit fields is what Pat mentioned. If the SQL Server field allows Nulls, Access can barf. The solution is to give it a default value of 0 and change the setting to not allow Nulls. At least that has worked for me.

I ran into another issue on a client's database after I helped him upsize to SQL Server. He had yes/no fields that he was adding up. It was like a survey result thing, with multiple yes/no fields (yeah, I know, not my design). When converted to SQL Server, we ran into an error where you can't do math on a bit field. You either have to change the data type to tinyint or something, or use CAST in your calculation.

 

[isladogs]

Paul
See post #7. Perhaps you missed it.