Anton Greffrath
asked on
MS Access to SQL Conversion - Bit and Yes/No Datatypes
Hi Experts. I have converted a customer's MS Access 2016 MDB Database to n SQL Server 2012 Database. All went well except the MS Access Yes/No fields that was converted to the SQL Server 2012 Database's Bit datatypes.
I expected that the Yes/No (MS Access) would translate directly to SQL Server 1 (True) and 0 (False). Not so, to be honest, I do not know what happened. In MS Access the values were Yes (-1) and No (0), but after the conversion I see that there are 0 ; 1 ; -1 and Null values in the field.
I read an article that indicated that SQL Server allows for 0 ; 1 and Null values in the Bit datatype definition, and in another article that the Bit data type allows for Yes and No Values or fields that can only contain one of 2 values.
Not surprising then that I am totally confused. I need to rectify the issue on the SQL Database but do not know how. Is there someone that can assist?
I expected that the Yes/No (MS Access) would translate directly to SQL Server 1 (True) and 0 (False). Not so, to be honest, I do not know what happened. In MS Access the values were Yes (-1) and No (0), but after the conversion I see that there are 0 ; 1 ; -1 and Null values in the field.
I read an article that indicated that SQL Server allows for 0 ; 1 and Null values in the Bit datatype definition, and in another article that the Bit data type allows for Yes and No Values or fields that can only contain one of 2 values.
Not surprising then that I am totally confused. I need to rectify the issue on the SQL Database but do not know how. Is there someone that can assist?
In SQL Server, the values are stored as 0, 1, or Null.
The ODBC driver converts these to VBA values: 0, -1, or Null which equals False, True, and Null.
Thus, a normal query will return 0, -1, or Null.
However, a pass-through query will, by definition, return the native values of SQL Server: 0, 1, or Null.
/gustav
The ODBC driver converts these to VBA values: 0, -1, or Null which equals False, True, and Null.
Thus, a normal query will return 0, -1, or Null.
However, a pass-through query will, by definition, return the native values of SQL Server: 0, 1, or Null.
/gustav
The correct data type is - as the others already wrote - BIT.
Access/Jet and ODBC will handle this. The only caveat is: You cannot longer use numeric Boolean comparisons in Access using -1/True. Here you need to switch to Not 0/False.
Access/Jet and ODBC will handle this. The only caveat is: You cannot longer use numeric Boolean comparisons in Access using -1/True. Here you need to switch to Not 0/False.
I think part of what you read as well that when used with Access, a bit field in SQL cannot contain a Null. It must always a value. That has more to do with how JET/ACE works rather then anything to do with the data type.
Jim.
Jim.
<<@Jim: BIT allows NULL.>>
Yes it allows null, but it can drive the cursor handling in JET/ACE a bit crazy resulting in #Deleted appearing in result sets. Doesn't always happen, but can in certain circumstances.
The "fix" is to always default bit fields to 0 and not allow nulls in the field.
Jim.
Yes it allows null, but it can drive the cursor handling in JET/ACE a bit crazy resulting in #Deleted appearing in result sets. Doesn't always happen, but can in certain circumstances.
The "fix" is to always default bit fields to 0 and not allow nulls in the field.
Jim.
I should add that this a known, long standing thing with JET/ACE and ODBC, as are floats, and triggers that change key fields. It's a by-product of the way it's cursor handling was designed.
That's why you always want to add a Time Stamp field to a SQL table if you can. JET/ACE will latch onto that for handling the cursor and you'll avoid problems. But if you don't, then bits, floats, and triggers can have an impact.
Jim.
That's why you always want to add a Time Stamp field to a SQL table if you can. JET/ACE will latch onto that for handling the cursor and you'll avoid problems. But if you don't, then bits, floats, and triggers can have an impact.
Jim.
If you have a lot of code/queries that specifically checks for -1 rather than True, your best option is to convert to SmallInt rather than Bit for the Y/N fields.
In either case, deselect the AllowNulls checkbox and provide a default of True (1 or -1 depending on how you decided the previous suggestion) or False (0) depending on what makes sense.
In either case, deselect the AllowNulls checkbox and provide a default of True (1 or -1 depending on how you decided the previous suggestion) or False (0) depending on what makes sense.
No points please. I agree with Pat that SmallInt is the way to go. It will hold -1, +1, 0 and null as necessary. Best practice is to check for 0 or Not 0, rather than checking for -1 or True, since "true" values may be +1 or -1 depending on where they were set.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pat. . . . . .
Bit can save 0 or 1 or NULL in SQL Server.
Sample Example for you
Open in new window
NOTE - If you pass String like 'True' or 'False' string then SQL Server will internally convert it to 0 or 1.
Output
Open in new window
Note - Also note that if you try to pass any string other than True / False it will not work.. Please check below-
Open in new window
Refer more at - https://msdn.microsoft.com/en-IN/library/ms177603.aspx
Hope it helps !